Wadi Market online store

SELECT date_trunc('month', o.ordered_at)::date AS month,
       count(DISTINCT o.order_id) AS orders,
       sum(oi.quantity * oi.unit_price - oi.discount_amount) AS item_revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status IN ('delivered', 'shipped', 'paid')
GROUP BY month
ORDER BY month;

SELECT o.order_id, o.ordered_at, o.status,
       sum(oi.quantity * oi.unit_price - oi.discount_amount) + o.shipping_fee AS order_total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.ordered_at DESC
LIMIT 20;

SELECT c.customer_id, c.first_name, c.last_name, c.signup_date
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.signup_date;

SELECT top.category_name AS department,
       sum(oi.quantity) AS units
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
JOIN categories AS leaf ON leaf.category_id = p.category_id
LEFT JOIN categories AS mid ON mid.category_id = leaf.parent_category_id
JOIN categories AS top ON top.category_id = coalesce(mid.parent_category_id, mid.category_id, leaf.category_id)
GROUP BY top.category_name
ORDER BY units DESC;

SELECT order_id,
       count(*) AS payment_rows,
       count(*) FILTER (WHERE status = 'captured') AS captured_rows,
       sum(amount) FILTER (WHERE status = 'captured') AS captured_amount
FROM payments
GROUP BY order_id
HAVING count(*) > 1
ORDER BY payment_rows DESC, order_id
LIMIT 20;