Part 22 - AMZN SQL Interview Questions 2022 | Subquery |SUM() RANK() SQL FUNCTION | Beginner Level

Windows SQL Function RANK() to solve the question.

Following is the CREATE and INSERT statement for table covered in this video

-- CREATE TABLE SCRIPT TAXI_RIDES

CREATE TABLE IF NOT EXISTS TAXI_RIDES
(
ID INT
, CATEGORY VARCHAR(20)
, PURPOSE VARCHAR(20)
, MILES FLOAT
, CHARGE FLOAT
, EXPENSE_DATE DATE

);

-- INSERT SCRIPT TAXI_RIDES

INSERT INTO TAXI_RIDES VALUES
(1,'BUSINESS','Meeting', 21.5, 47.8, TO_DATE('2021-10-04', 'YYYY-MM-DD') ),
(2,'BUSINESS','Customer Visit',30.8, 54.2, TO_DATE('2021-11-10', 'YYYY-MM-DD') ),
(3,'BUSINESS','Meal', 5, 12.4 , TO_DATE('2021-12-23', 'YYYY-MM-DD') ),
(4,'BUSINESS','Conference', 10, 24.2 , TO_DATE('2022-01-23', 'YYYY-MM-DD') ),
(5,'BUSINESS','Customer Visit',30.8, 54.2 , TO_DATE('2022-03-08', 'YYYY-MM-DD') ),
(6,'BUSINESS','business lunch', 5, 12.4 , TO_DATE('2022-03-24', 'YYYY-MM-DD') ),
(7,'BUSINESS','Meeting', 10.4, 25.7 , TO_DATE('2021-10-28', 'YYYY-MM-DD') ),
(8,'BUSINESS','Team lunch', 7, 18.4 , TO_DATE('2021-12-08', 'YYYY-MM-DD') ),
(9,'BUSINESS','Conference', 16, 35.8 , TO_DATE('2022-04-08', 'YYYY-MM-DD') ),
(10,'BUSINESS','Customer Visit', 30, 70.4, TO_DATE('2022-05-07', 'YYYY-MM-DD') ),
(11,'PERSONAL','Lunch', 4, 12.5, TO_DATE('2021-08-04', 'YYYY-MM-DD') ),
(12,'PERSONAL','Meet Friends', 12 , 25.2, TO_DATE('2021-11-12', 'YYYY-MM-DD') ),
(13,'PERSONAL','Dinner', 5, 14.2, TO_DATE('2022-12-27', 'YYYY-MM-DD') ),
(14,'PERSONAL','Movie', 3, 12.4 , TO_DATE('2022-12-28', 'YYYY-MM-DD') ),
(15,'PERSONAL','Meet Friends', 16, 34.2 , TO_DATE('2022-02-14', 'YYYY-MM-DD') ),
(16,'PERSONAL','Dinner', 10, 24.5 , TO_DATE('2022-03-12', 'YYYY-MM-DD') ),
(17,'PERSONAL','Concert', 21, 40.7 , TO_DATE('2021-12-21', 'YYYY-MM-DD') ),
(18,'PERSONAL','Lunch', 4, 12.7 , TO_DATE('2022-01-08', 'YYYY-MM-DD') ),
(19,'PERSONAL','Concert', 10, 27.2 , TO_DATE('2022-06-17', 'YYYY-MM-DD') ),
(20,'PERSONAL','Dinner', 8, 16.8, TO_DATE('2022-07-12', 'YYYY-MM-DD') ) ;

-------- select query --------

SELECT * FROM TAXI_RIDES

-- QN - Display purposes within each categories having the lowest miles driven

SELECT X.CATEGORY,X.PURPOSE, X.TOTAL_MILES
FROM
(
SELECT CATEGORY , PURPOSE , SUM (MILES) AS TOTAL_MILES,
RANK () OVER (PARTITION BY CATEGORY ORDER BY SUM(MILES) ) AS RANK
FROM TAXI_RIDES
GROUP BY CATEGORY , PURPOSE
) X
WHERE X.RANK = 1

Following is the link where I created a playlist for Amazon SQL questions series where all the related videos are placed in this playlist and going forward it will be added in this playlist as well.

[HaryanvigirlinCanada]

New to ur channel. Thanks for making such videos. I have online sql assessment coming up for amazon. I really appreciate it. Will watch ur videos thanks