I have the following tables:

Orders:

```
|order_id|customer_id|      order_date      |item_id|quantity|
|--------|-----------|----------------------|-------|--------|
|   1    |  1        |  2020-06-01 00:00:00 |   1   |      10|
|   2    |  1        |  2020-06-08 00:00:00 |   2   |      10|
|   3    |  2        |  2020-06-02 00:00:00 |   1   |      5 |
|   4    |  3        |  2020-06-03 00:00:00 |   3   |      5 |
|   5    |  4        |  2020-06-04 00:00:00 |   4   |      1 |
|   6    |  4        |  2020-06-05 00:00:00 |   5   |      5 |
|   7    |  5        |  2020-06-05 00:00:00 |   1   |      10|
|   8    |  5        |  2020-06-14 00:00:00 |   4   |      5 |
|   9    |  5        |  2020-06-21 00:00:00 |   3   |      5 |
|   5    |  4        |  2020-06-08 00:00:00 |   4   |      1 |

```

Items:

```
|item_id|item_name          |item_category|
|-------|---------  --------|-------------|
|   1   |LC Alg. Book       |Book         |
|   2   |LC DB. Book        |Book         |
|   3   |LC SmarthPhone     |Phone        |
|   4   |LC Phone 2020      |Phone        |
|   5   |LC SmartGlass      |Glasses      |
|   6   |LC T-Shirt XL      |T-Shirt      |

```

Im creating a query that should return a report on how many units in each item\_category have been ordered on each day of the week. The report should look like:

```
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Category   | Monday    | Tuesday   | Wednesday | Thursday  | Friday    | Saturday  | Sunday    |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Book       | 20        | 5         | 0         | 0         | 10        | 0         | 0         |
| Glasses    | 0         | 0         | 0         | 0         | 5         | 0         | 0         |
| Phone      | 0         | 0         | 5         | 1         | 0         | 0         | 10        |
| T-Shirt    | 0         | 0         | 0         | 0         | 0         | 0         | 0         |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

```

Following is my query, which is accurate for all but one case - 'Phone' on Thursday should be qty = 1 but my query returns 0. This quantity is returned accurately in cte2, but I loose it in cte3. Can't figure out why. What's wrong with my query?

```
with cte1 as 
    (select * from
        (select "Monday" as day_name union 
        select "Tuesday" union
        select "Wednesday" union
        select "Thursday " union
        select "Friday" union
        select "Saturday " union
        select "Sunday" ) t1
     cross join
        (select distinct item_category from items) t2),
cte2 as 
    (select i.item_category, 
        dayname(order_date) as day_name,
        sum(quantity) as tt_qty
    from orders o
    inner join items i 
    on i.item_id  = o.item_id 
    group by i.item_category, dayname(order_date)),
cte3 as
    (select cte1.day_name,
        cte1.item_category,
        coalesce(cte2.tt_qty, 0) as tt_qty
    from cte1
    left join cte2
    on cte2.day_name = cte1.day_name
    and cte2.item_category = cte1.item_category)
select item_category as category, 
    coalesce (max(case when cte3.day_name ='Monday' then cte3.tt_qty 
        end),0) as Monday,
    coalesce (max(case when cte3.day_name ='Tuesday' then cte3.tt_qty 
        end),0) as Tuesday,
    coalesce (max(case when cte3.day_name ='Wednesday' then cte3.tt_qty 
        end),0) as Wednesday,
    coalesce (max(case when cte3.day_name ='Thursday' then cte3.tt_qty
        end),0) as Thursday,
    coalesce (max(case when cte3.day_name ='Friday' then cte3.tt_qty 
        end),0) as Friday,
    coalesce (max(case when cte3.day_name ='Saturday' then cte3.tt_qty 
        end),0) as Saturday,
    coalesce (max(case when cte3.day_name ='Sunday' then cte3.tt_qty 
        end),0) as Sunday       
from cte3
group by item_category
order by 1

```

DBfiddle link: <https://www.db-fiddle.com/#&togetherjs=sD7fqL9voK>