Project: Shop Reporting Database

CREATE SCHEMA reporting;

CREATE VIEW reporting.order_lines AS
SELECT o.order_id, o.order_date, o.status,
       c.customer_id, c.first_name || ' ' || c.last_name AS customer, c.city, c.state,
       p.product_id, p.product_name, p.category,
       oi.quantity, oi.unit_price,
       oi.quantity * oi.unit_price AS line_total,
       o.status <> 'cancelled' AS counts_as_sale
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id;

SELECT count(*) AS lines,
       count(*) FILTER (WHERE counts_as_sale) AS sale_lines,
       sum(line_total) FILTER (WHERE counts_as_sale) AS revenue
FROM reporting.order_lines;

CREATE SCHEMA reporting;
CREATE VIEW reporting.order_lines AS
SELECT o.order_id, o.order_date, o.status, c.customer_id,
       c.first_name || ' ' || c.last_name AS customer, c.city,
       p.product_id, p.product_name, p.category, oi.quantity,
       oi.quantity * oi.unit_price AS line_total,
       o.status <> 'cancelled' AS counts_as_sale
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id;

CREATE VIEW reporting.monthly_sales AS
SELECT date_trunc('month', order_date)::date AS month,
       count(DISTINCT order_id) FILTER (WHERE counts_as_sale) AS orders,
       count(DISTINCT order_id) FILTER (WHERE status = 'cancelled') AS cancelled,
       sum(line_total) FILTER (WHERE counts_as_sale) AS revenue,
       sum(sum(line_total) FILTER (WHERE counts_as_sale))
         OVER (ORDER BY date_trunc('month', order_date)::date) AS running_revenue
FROM reporting.order_lines
GROUP BY 1;

SELECT * FROM reporting.monthly_sales ORDER BY month;

CREATE SCHEMA reporting;
CREATE VIEW reporting.order_lines AS
SELECT o.order_id, o.order_date, o.status, o.customer_id,
       oi.quantity * oi.unit_price AS line_total,
       o.status <> 'cancelled' AS counts_as_sale
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id;

CREATE VIEW reporting.customer_value AS
SELECT c.customer_id,
       c.first_name || ' ' || c.last_name AS customer,
       c.city,
       count(DISTINCT ol.order_id) FILTER (WHERE ol.counts_as_sale) AS orders,
       coalesce(sum(ol.line_total) FILTER (WHERE ol.counts_as_sale), 0) AS spent,
       max(ol.order_date) AS last_order,
       CASE
         WHEN coalesce(sum(ol.line_total) FILTER (WHERE ol.counts_as_sale), 0) >= 20000 THEN 'Gold'
         WHEN coalesce(sum(ol.line_total) FILTER (WHERE ol.counts_as_sale), 0) >= 5000 THEN 'Silver'
         WHEN count(ol.order_id) > 0 THEN 'Bronze'
         ELSE 'Not yet ordered'
       END AS tier,
       dense_rank() OVER (ORDER BY coalesce(sum(ol.line_total) FILTER (WHERE ol.counts_as_sale), 0) DESC) AS spend_rank
FROM customers AS c
LEFT JOIN reporting.order_lines AS ol ON ol.customer_id = c.customer_id
GROUP BY c.customer_id;

SELECT spend_rank, customer, city, orders, spent, tier
FROM reporting.customer_value
ORDER BY spend_rank, customer
LIMIT 6;

SELECT tier, count(*) AS customers
FROM reporting.customer_value
GROUP BY tier
ORDER BY min(spend_rank);

CREATE SCHEMA reporting;
CREATE VIEW reporting.order_lines AS
SELECT o.order_id, o.order_date, p.category,
       oi.quantity, oi.quantity * oi.unit_price AS line_total,
       o.status <> 'cancelled' AS counts_as_sale
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id;

CREATE FUNCTION reporting.sales_between(p_from date, p_to date)
RETURNS TABLE (category text, orders bigint, units bigint, revenue numeric, share_pct numeric)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
  IF p_to < p_from THEN
    RAISE EXCEPTION 'End date % is before start date %', p_to, p_from;
  END IF;

  RETURN QUERY
    SELECT ol.category::text,
           count(DISTINCT ol.order_id),
           sum(ol.quantity),
           sum(ol.line_total),
           round(100 * sum(ol.line_total) / sum(sum(ol.line_total)) OVER (), 1)
    FROM reporting.order_lines AS ol
    WHERE ol.counts_as_sale
      AND ol.order_date >= p_from AND ol.order_date < p_to + 1
    GROUP BY ol.category
    ORDER BY 4 DESC;
END;
$$;

SELECT * FROM reporting.sales_between('2025-01-01', '2025-03-31');

CREATE SCHEMA reporting;

CREATE MATERIALIZED VIEW reporting.product_performance AS
SELECT p.product_id, p.product_name, p.category, p.stock,
       coalesce(sum(oi.quantity) FILTER (WHERE o.status <> 'cancelled'), 0) AS units_sold,
       coalesce(sum(oi.quantity * oi.unit_price) FILTER (WHERE o.status <> 'cancelled'), 0) AS revenue,
       CASE
         WHEN p.stock = 0 THEN 'Restock now'
         WHEN coalesce(sum(oi.quantity) FILTER (WHERE o.status <> 'cancelled'), 0) = 0 THEN 'Not selling'
         ELSE 'OK'
       END AS action
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.product_id
LEFT JOIN orders AS o ON o.order_id = oi.order_id
GROUP BY p.product_id;

CREATE UNIQUE INDEX product_performance_id_idx ON reporting.product_performance (product_id);

REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.product_performance;

SELECT product_name, stock, units_sold, revenue, action
FROM reporting.product_performance
WHERE action <> 'OK' OR revenue > 15000
ORDER BY revenue DESC;

CREATE INDEX order_items_order_idx ON order_items (order_id);
CREATE INDEX order_items_product_idx ON order_items (product_id);
CREATE INDEX orders_customer_idx ON orders (customer_id);
CREATE INDEX orders_date_idx ON orders (order_date);
CREATE INDEX orders_open_date_idx ON orders (order_date) WHERE status IN ('pending', 'shipped');

SELECT tablename, indexname
FROM pg_indexes
WHERE schemaname = 'public' AND tablename IN ('orders', 'order_items')
ORDER BY tablename, indexname;

CREATE ROLE reporting_team NOLOGIN;
GRANT USAGE ON SCHEMA reporting TO reporting_team;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO reporting_team;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA reporting TO reporting_team;
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting GRANT SELECT ON TABLES TO reporting_team;

CREATE ROLE farhan LOGIN PASSWORD 'a-long-random-password' IN ROLE reporting_team;