Wadi Market analytics mart

SELECT d.year, d.month, d.month_name,
       sum(f.net_amount) AS net_sales
FROM fact_sales AS f
JOIN dim_date AS d ON d.date_key = f.date_key
GROUP BY d.year, d.month, d.month_name
ORDER BY d.year, d.month;

SELECT p.category,
       sum(f.net_amount) AS net_sales,
       sum(f.net_amount - f.cost_amount) AS gross_margin
FROM fact_sales AS f
JOIN dim_product AS p ON p.product_key = f.product_key
GROUP BY p.category
ORDER BY net_sales DESC;

SELECT c.region, d.year, sum(f.net_amount) AS net_sales
FROM fact_sales AS f
JOIN dim_customer AS c ON c.customer_key = f.customer_key
JOIN dim_date AS d ON d.date_key = f.date_key
WHERE c.customer_key <> 0
GROUP BY c.region, d.year
ORDER BY c.region, d.year;

SELECT ch.channel_name,
       count(*) AS sessions,
       avg(s.reached_product_page::int) AS product_page_rate,
       avg(s.added_to_cart::int) AS cart_rate,
       avg(s.purchased::int) AS conversion_rate
FROM fact_web_sessions AS s
JOIN dim_channel AS ch ON ch.channel_key = s.channel_key
GROUP BY ch.channel_name;

SELECT d.year, d.quarter,
       sum(m.spend_amount) AS spend,
       sum(m.budget_amount) AS budget
FROM fact_marketing_spend AS m
JOIN dim_date AS d ON d.date_key = m.month_key
GROUP BY d.year, d.quarter
ORDER BY d.year, d.quarter;