Amazon BIE L5

Revenue:
customer_id (varchar),
product_id (varchar),
month_start_date (date), 2022-03-01
revenue_amt (decimal)

1. Display the top 10 revenue generating products for year 2020.

select top 10 product_id , sum(revenue_amt) as total
from Revenue
where year(month_start_date) = 2020
group by product_id
order by sum(revenue_amt) desc

2. What is the revenue by customer for 2020, 2021, and 2022.
   Note, the output must have 4 columns: customer_id, revenue_2020, revenue_2021, revenue_2022.

select customer_id,
sum(revenue_amt) over (partition by customer_id, year(month_start_date) order by year(month_start_date) desc rows preceeding 0 and current row) revnue_2022,
sum(revenue_amt) over (partiton by customer_id, year(month_start_date) order by year(month_start_date) desc rows preceeding 1 and precceding 1) revenue_2021,
sum(revenue_amt) over (partiton by customer_id, year(month_start_date) order by year(month_start_date) desc rows preceeding 2 and precceding 2) revenue_2020
from revenue

3. Display the top 5 customers by revenue for 2020, 2021, and 2022
   with cust_rank as (
   select sub.customer_id,sub.year, dense_rank() over (partition by sub.year order by sub.total desc) as rnk from
   (select customer_id, year(month_start_date) as year sum(revenue) total from Revenue group by customer_id,year)sub)

select customer_id, year from cust_rank where rnk <= 5

4. Display What is the cumulative sum of revenue by month for each year. Sample output below

+-----------------------------+
| month_start_date sum |
+-----------------------------+
| ------------------- ------ |
| 1/2/2017 650 |
| 1/9/2017 900 |
| 1/4/2018 600 |
| 1/6/2018 800 |
| 1/7/2018 950 |
| 1/1/2019 800 |
| 1/4/2019 900 |
| 1/5/2019 1700 |
+-----------------------------+

select month_start_date, sum(revenue) over (partition by year(month_start_date) order by month_start_date) sum from revenue