Apache Impala – Data Analysis – Problem Scenario-1

One of the problem scenarios that can aid in analytical thought and problem-solving is shown below. SQL Server, MySQL, any RDBMS product, Apache Hive, Apache Impala, etc. can all be used for this.

Sample dataset:
You can replace VARCHAR(50) with STRING if you are using Apache Hive or Impala.

DROP TABLE IF EXISTS tbVehicles;

CREATE TABLE tbVehicles(
VehicleID INT,
PlateNumber VARCHAR(50),
PlateCategory VARCHAR(50),
VehicleMake VARCHAR(50),
VehicleModel VARCHAR(50),
VehicleDescription VARCHAR(50),
RatePerDay FLOAT
);

INSERT INTO tbVehicles VALUES
(91,’DXB1111A’, ‘Light Vehicle’, ‘NISSAN’, ‘SUNNY 1.6L’, ‘4 Door Saloon, Automatic’, 99.99),
(92,’DXB2222B’, ‘Light Vehicle’, ‘TOYOTA’, ‘ALTIS 1.6L’, ‘4 Door Saloon, Automatic’, 99.99),
(93,’SHJ3333C’, ‘Light Vehicle’, ‘HONDA’, ‘CIVIC 1.8L’, ‘4 Door Saloon, Automatic’, 119.99),
(94,’AD1555E’, ‘Truck’, ‘NISSAN’,’CABSTAR 3.0L’, ‘Lorry, Manual ‘, 89.99),
(95,’AD3666F’, ‘Truck’, ‘OPEL’,’COMBO 1.6L’, ‘Van, Manual’, 69.99);

— Create tbCustomers table
DROP TABLE IF EXISTS tbCustomers;

CREATE TABLE tbCustomers(
CustomerID INT,
CustomerName VARCHAR(50),
PassportNumber VARCHAR(50),
Address VARCHAR(50),
Phone VARCHAR(50),
Discount FLOAT);

INSERT INTO tbCustomers VALUES
(1001, ‘Tan Ah Teck’, ‘SS12345’, ‘8 Happy Ave’, ‘11888888’, 0.1),
(1002, ‘Mohammed Ali’, ‘SS12349’, ‘1 Kg Java’, ‘12999999’, 0.15),
(1003, ‘Kumar’, ‘SS12346′,’5 Serangoon Road’, ‘13555555’, 0),
(1004, ‘Kevin Jones’,’SS12346′, ‘2 Sunset boulevard’, ‘13222222’, 0.2);

— Create tbTransactions’ table
DROP TABLE IF EXISTS tbTransactions;

CREATE TABLE tbTransactions(
RentalId INT,
VehicleID INT,
CustomerID INT,
StartDate DATETIME,
EndDate DATETIME,
LastUpdated DATETIME
);

INSERT INTO tbTransactions VALUES
(1, 91, 1001, ‘2012-01-01’, ‘2012-01-21’, NULL),
(2, 91, 1001, ‘2012-02-01’, ‘2012-02-05’, NULL),
(3, 94, 1003, ‘2012-01-05’, ‘2012-01-31’, NULL),
(4, 95, 1004, ‘2012-01-20’, ‘2012-02-20’, NULL),
(5, 91, 1001, ‘2012-02-01’, ‘2012-02-21’, NULL),
(6, 91, 1001, ‘2012-02-02’, ‘2012-03-05’, NULL),
(7, 94, 1003, ‘2012-02-05’, ‘2012-03-31’, NULL),
(8, 95, 1004, ‘2012-02-20’, ‘2012-03-20’, NULL),
(9, 94, 1003, ‘2012-03-21’, ‘2012-03-22’, NULL);

As noted earlier, you can replace VARCHAR(20) with STRING if you are using big data ecosystem components such as Apache Hive or Apache Impala.

Now the requirement is –

List out the transactions with columns Plate Information, Actual rental rate, customer details, car renting period, the discount, duration of days, total discount entitled, actual price if no discount is provided, and Final invoice to the customers.
Compute the actual rental price and discounted price;
Few customers have got the discount regardless of their rental period.
All the customers will get a discount of 20% if the rental period is 7 or more days.
CAST the result from DOUBLE to DECIMAL(8,2)

Solution (SQL Server):

SELECT v.PlateNumber,
v.RatePerDay,
c.CustomerName,
r.StartDate ,
r.EndDate,
c.Discount,
DATEDIFF(D,r.StartDate, r.EndDate) AS ‘DurationDays’,
c.discount*100 AS DiscountEntitled,
DATEDIFF(D,r.StartDate, r.EndDate)* RatePerDay AS ActualPrice,
— Compute the rental price
— Preferred customer has discount, 20% discount for 7 or more days
— CAST the result from DOUBLE to DECIMAL(8,2)
CAST(
IIF(DATEDIFF(D,r.StartDate, r.EndDate) < 7,
DATEDIFF(D, r.StartDate, r.EndDate)*RatePerDay*(1-discount),
DATEDIFF(D, r.StartDate, r.EndDate)*RatePerDay*(1-discount)*0.8) AS DECIMAL(8,2))
AS FinalInvoice
FROM tbTransactions AS r
INNER JOIN tbVehicles AS v ON r.VehicleID=v.VehicleID
INNER JOIN tbCustomers AS c on r.CustomerID=c.CustomerID;

Solution (Apache Impala):

SELECT v.PlateNumber,
v.RatePerDay,
c.CustomerName,
r.StartDate ,
r.EndDate,
c.Discount,
DATEDIFF(r.EndDate,r.StartDate) AS ‘DurationDays’,
c.discount*100 AS DiscountEntitled,
DATEDIFF(r.EndDate, r.StartDate)* RatePerDay AS ActualPrice,
— Compute the rental price
— Preferred customer has discount, 20% discount for 7 or more days
— CAST the result from DOUBLE to DECIMAL(8,2)
CAST(
IF(DATEDIFF(r.EndDate, r.StartDate) < 7,
DATEDIFF(r.EndDate, r.StartDate)*RatePerDay*(1-discount),
DATEDIFF(r.EndDate, r.StartDate)*RatePerDay*(1-discount)*0.8) AS DECIMAL(8,2))
AS FinalInvoice;