### 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
```