FInding market share and year change with SQL

Question

Here for database schema

The Case Problem:

1. What was the total number of purchases of dairy products for each month of 2020 (i.e., the total_sales)?
2. What was the total share of dairy products (out of all products purchased) for each month of 2020 (i.e., the market_share)?
3. For each month of 2020, what was the percentage increase or decrease in total monthly dairy purchases compared to the same month in 2019 (i.e., the year_change)? As a result, it interested in these three categories (which they treat as dairy): ‘whole milk’, 'yogurt' and 'domestic eggs'.

The instruction: Order your query by month in ascending order. Both month and total_sales should be expressed as integers, and market_share and year_change should be percentages rounded to two decimal places (e.g., 27.95% becomes 27.95).

Your query will need to return a table that resembles the following, including the same column names.

Here for the code:

with purchases_2019 as (SELECT p1.month as month,COUNT(p1.purchase_id) as count_2
 FROM purchases_2019 as p1
 LEFT JOIN categories as cat ON p1.purchase_id=cat.purchase_id
 WHERE cat.category IN ('whole milk', 'yogurt' ,'domestic eggs')
 GROUP BY p1.month
 ORDER BY p1.month ASC),
    purchases_2020 as ( SELECT to_char(CAST(p2.fulldate AS DATE),'MM')::int as month, 
 COUNT(p2.purchaseid) as total_sales, 
 ROUND((COUNT(p2.purchaseid)*100::numeric/18277)::numeric,2) as market_share
 FROM purchases_2020 as p2
 LEFT JOIN categories as cat ON p2.purchaseid=cat.purchase_id 
 WHERE cat.category IN ('whole milk', 'yogurt' ,'domestic eggs')
 GROUP BY month
 ORDER BY month ASC)
    SELECT t2.month,t2.total_sales,t2.market_share,
    ROUND(((t2.total_sales-t1.count_2)*100::numeric/t1.count_2) ,2) as year_change
    FROM purchases_2020 as t2 
    INNER JOIN purchases_2019 as t1 ON t2.month=t1.month

The result is obtained:

But it's still wrong answer. I don't have any idea. Can you give me some enlightenment? Thank You

Answer 1

with p as 
(select 
    extract(month from to_date(b.full_date, 'YYYY/MM/DD')) as "month",
    sum(case when c.category in ('whole milk', 'yogurt', 'domestic eggs') then 1 else 0 end) as "old_sales"
from purchases_2019 b left join categories c 
    on b.purchase_id = c.purchase_id
group by 1
order by 1),

temp as
(select 
    extract(month from to_date(a.fulldate,'YYYY/MM/DD')) as "month",
    sum(case when c.category in ('whole milk', 'yogurt', 'domestic eggs') then 1 else 0 end) as "total_sales",
    round(100 * sum(case when c.category in ('whole milk', 'yogurt', 'domestic eggs') then 1 else 0 end)::numeric 
          / count(a.purchaseid),2) as "market_share"
from 
    purchases_2020 a left join categories c
        on a.purchaseid = c.purchase_id 
group by 1
order by 1)

select 
    temp.month, total_sales, market_share,
    round(100 * (total_sales - old_sales)::numeric / old_sales, 2) as "year_change"    
from temp left join p on temp.month = p.month;

Answer 2

Why 18277?

This part:

ROUND((COUNT(p2.purchaseid)*100::numeric/18277)::numeric,2) as market_share

Could there be an error in the market_share calculation?

I think in this code, only 3 categories are calculated, but market share should not be all sales/3 category sales?

Just an idea.