Amazon BIE 1 interview SQL question and answer

Question 1

month            revenue
2001-01-01         650
2001-02-01        1200
2001-03-01         100
2001-04-01        1100
.
.
.
.2020-12-01       5000

Write SQL query to get the running total of the revenue to get below desired output: With Year and revenue and running total of revenue

Answer

SELECT 
    YEAR(month) AS Year,
    Revenue,
    SUM(revenue) OVER (ORDER BY YEAR(month)) AS Running_Total
FROM Table
ORDER BY YEAR(month);

Question 2

Given EMPLOYEES table with 3 columns: EMPLID, EMP_NAME, SUPERVISOR_ID.

Write a query to return the following columns for every employee: name of the employee and name of the employee's supervisor.

Answer

SELECT E.EMP_NAME,
        S.EMP_NAME
FROM EMPLOYEES E
LEFT JOIN EMPLOYEES S
 ON E.SUPERVISOR_ID = S.EMPLID;