SQL Interview Questions – Sales by Day of the Week

Problem Description –

You are the business owner and would like to obtain a sales report for category items and the day of the week.

Write an SQL query to report how many units in each category have been ordered on each day of the week.

Return the result table ordered by category.

The query result format is in the following example.

Difficulty Level – Hard

Problem Link – sales by day of week

Solution –

SELECT
	item_category as category,
    SUM(CASE WHEN weekdays = 0 THEN total_quantity ELSE 0 END) as Monday,
    SUM(CASE WHEN weekdays = 1 THEN total_quantity ELSE 0 END) as Tuesday,
    SUM(CASE WHEN weekdays = 2 THEN total_quantity ELSE 0 END) as Wednesday,
    SUM(CASE WHEN weekdays = 3 THEN total_quantity ELSE 0 END) as Thursday,
    SUM(CASE WHEN weekdays = 4 THEN total_quantity ELSE 0 END) as Friday,
    SUM(CASE WHEN weekdays = 5 THEN total_quantity ELSE 0 END) as Saturday,
    SUM(CASE WHEN weekdays = 6 THEN total_quantity ELSE 0 END) as Sunday
FROM (
SELECT 
    i.item_category,
    WEEKDAY(o.order_date) as weekdays,
    SUM(o.quantity) as total_quantity
FROM Items as i LEFT JOIN Orders as o
ON o.item_id = i.item_id
GROUP BY 1, 2
) x 
GROUP BY 1
ORDER BY 1

To get the week days we use the WEEKDAY function. The weekday starts from 0- Monday to 6 – Sunday.

So first we will group the data by category and weekdays and calculate the total quantity ordered.

 SELECT 
    i.item_category,
    WEEKDAY(o.order_date) as weekdays,
    SUM(o.quantity) as total_quantity
FROM Items as i LEFT JOIN Orders as o
ON o.item_id = i.item_id
GROUP BY 1, 2

Next we need to pivot the data.

SELECT
    item_category as category,
    CASE WHEN weekdays = 0 THEN total_quantity ELSE 0 END as Monday,
    CASE WHEN weekdays = 1 THEN total_quantity ELSE 0 END as Tuesday,
    CASE WHEN weekdays = 2 THEN total_quantity ELSE 0 END as Wednesday,
    CASE WHEN weekdays = 3 THEN total_quantity ELSE 0 END as Thursday,
    CASE WHEN weekdays = 4 THEN total_quantity ELSE 0 END as Friday,
    CASE WHEN weekdays = 5 THEN total_quantity ELSE 0 END as Saturday,
    CASE WHEN weekdays = 6 THEN total_quantity ELSE 0 END as Sunday
FROM (
SELECT 
    i.item_category,
    WEEKDAY(o.order_date) as weekdays,
    SUM(o.quantity) as total_quantity
FROM Items as i LEFT JOIN Orders as o
ON o.item_id = i.item_id
GROUP BY 1, 2
) x 

Now, all we need to do is group the data by category and use the SUM function to calculate the total sales for each category by week days.

SELECT
	item_category as category,
    SUM(CASE WHEN weekdays = 0 THEN total_quantity ELSE 0 END) as Monday,
    SUM(CASE WHEN weekdays = 1 THEN total_quantity ELSE 0 END) as Tuesday,
    SUM(CASE WHEN weekdays = 2 THEN total_quantity ELSE 0 END) as Wednesday,
    SUM(CASE WHEN weekdays = 3 THEN total_quantity ELSE 0 END) as Thursday,
    SUM(CASE WHEN weekdays = 4 THEN total_quantity ELSE 0 END) as Friday,
    SUM(CASE WHEN weekdays = 5 THEN total_quantity ELSE 0 END) as Saturday,
    SUM(CASE WHEN weekdays = 6 THEN total_quantity ELSE 0 END) as Sunday
FROM (
SELECT 
    i.item_category,
    WEEKDAY(o.order_date) as weekdays,
    SUM(o.quantity) as total_quantity
FROM Items as i LEFT JOIN Orders as o
ON o.item_id = i.item_id
GROUP BY 1, 2
) x 
GROUP BY 1
ORDER BY 1