Amazon SQL Interview Question | Biggest Black Friday Discount | New 2024

We have a new SQL interview question from Amazon!

We’re figuring out which product had the biggest Black Friday discount:

Here is the workflow:
First, we use a CTE (Common Table Expression) to get the original price (from January 1, 2023) and the Black Friday price (from November 25, 2023) for each product using a CASE statement.

Next, we calculate the discount percentage by subtracting the Black Friday price from the original price, then dividing by the original price.

Finally, we ORDER BY the discount in descending order, so the biggest discounts appear at the top.

Get the full SQL code here :
/* Which Product has the
Biggest Black Friday Discount*/
with prices as(
select
product_name,
max(case when price_date ='2023-01-01'
then price end) as original,
max(case when price_date ='2023-11-25'
then price end) as black_friday
from black_friday_sales
group by product_name)

select
product_name,
(original-black_friday)/original
as discount
from prices
where black_friday is not null
order by 2 desc

Here is the data if you want to practice
product_name,category,price_date,price
iPhone 13,Electronics,2023-01-01,799.99
iPhone 13,Electronics,2023-03-15,749.99
iPhone 13,Electronics,2023-06-01,699.99
iPhone 13,Electronics,2023-09-15,649.99
iPhone 13,Electronics,2023-11-25,599.99
AirPods Pro,Electronics,2023-01-01,249.99
AirPods Pro,Electronics,2023-07-01,219.99
AirPods Pro,Electronics,2023-11-25,199.99
Nike Air Max,Footwear,2023-01-01,129.99
Nike Air Max,Footwear,2023-04-15,149.99
Nike Air Max,Footwear,2023-08-01,159.99
Nike Air Max,Footwear,2023-12-01,139.99

#sqlqueryinterviewquestionsandanswers
#sql
#dataanalysis
#dataanalyst

Видео Amazon SQL Interview Question | Biggest Black Friday Discount | New 2024 канала Absent Data