Project: Analysing a Shop

SELECT (SELECT COUNT(*) FROM customers) AS customers,
       (SELECT COUNT(*) FROM orders) AS orders,
       (SELECT COUNT(*) FROM products) AS products,
       (SELECT round(SUM(oi.quantity * oi.unit_price), 2)
        FROM order_items AS oi JOIN orders AS o ON o.id = oi.order_id
        WHERE o.status <> 'cancelled') AS revenue;

WITH monthly AS (
  SELECT strftime('%Y-%m', o.order_date) AS month,
         round(SUM(oi.quantity * oi.unit_price), 2) AS revenue
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY month
)
SELECT month, revenue,
       round(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change
FROM monthly
ORDER BY month;

WITH sales AS (
  SELECT p.name, SUM(oi.quantity) AS units, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items AS oi
  JOIN orders AS o ON o.id = oi.order_id
  JOIN products AS p ON p.id = oi.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY p.id, p.name
)
SELECT name, units, round(revenue, 2) AS revenue,
       round(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total
FROM sales
ORDER BY revenue DESC
LIMIT 5;

SELECT c.name,
       COUNT(DISTINCT o.id) AS orders,
       round(COALESCE(SUM(oi.quantity * oi.unit_price), 0), 2) AS spent,
       MIN(o.order_date) AS first_order,
       MAX(o.order_date) AS last_order
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status <> 'cancelled'
LEFT JOIN order_items AS oi ON oi.order_id = o.id
GROUP BY c.id, c.name
ORDER BY spent DESC;

WITH per_customer AS (
  SELECT customer_id, COUNT(*) AS orders
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY customer_id
)
SELECT COUNT(*) AS customers_who_ordered,
       COUNT(*) FILTER (WHERE orders > 1) AS repeat_customers,
       round(100.0 * COUNT(*) FILTER (WHERE orders > 1) / COUNT(*), 1) AS repeat_pct
FROM per_customer;

SELECT p.name, p.stock,
       COALESCE(SUM(oi.quantity) FILTER (WHERE o.status = 'pending'), 0) AS pending_units
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.id
LEFT JOIN orders AS o ON o.id = oi.order_id
GROUP BY p.id, p.name, p.stock
HAVING p.stock = 0 OR p.stock < pending_units
ORDER BY p.stock;

WITH revenue AS (
  SELECT c.country, p.category, round(SUM(oi.quantity * oi.unit_price), 2) AS revenue
  FROM customers AS c
  JOIN orders AS o ON o.customer_id = c.id
  JOIN order_items AS oi ON oi.order_id = o.id
  JOIN products AS p ON p.id = oi.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY c.country, p.category
)
SELECT country, category, revenue,
       CASE WHEN RANK() OVER (PARTITION BY country ORDER BY revenue DESC) = 1 THEN '★' ELSE '' END AS best
FROM revenue
ORDER BY country, revenue DESC;

WITH ranked AS (
  SELECT e.*, ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS pos
  FROM employees AS e
)
SELECT COALESCE(d.name, '(no department)') AS department,
       COUNT(r.id) AS headcount,
       COALESCE(SUM(r.salary), 0) AS payroll,
       round(AVG(r.salary)) AS avg_salary,
       MAX(CASE WHEN r.pos = 1 THEN r.first_name || ' ' || r.last_name END) AS top_earner
FROM departments AS d
FULL JOIN ranked AS r ON r.department_id = d.id
GROUP BY d.id, d.name
ORDER BY payroll DESC;

CREATE VIEW sales_lines AS
SELECT o.order_date, strftime('%Y-%m', o.order_date) AS month,
       c.name AS customer, c.country, p.name AS product, p.category,
       oi.quantity, oi.quantity * oi.unit_price AS line_total
FROM order_items AS oi
JOIN orders AS o ON o.id = oi.order_id
JOIN customers AS c ON c.id = o.customer_id
JOIN products AS p ON p.id = oi.product_id
WHERE o.status <> 'cancelled';

SELECT country, category, round(SUM(line_total), 2) AS revenue
FROM sales_lines
GROUP BY country, category
ORDER BY country, revenue DESC;