Write Operational and Analytical Queries

SELECT  so.order_number,  so.status AS order_status,  so.ordered_at,  c.display_name AS customer_name,  a.label AS address_label,  a.city AS ship_city,  oi.line_no,  p.sku,  p.product_name,  oi.quantity,  oi.unit_price_cents,  oi.line_total_centsFROM sales_order AS soJOIN customer AS c ON c.customer_id = so.customer_idJOIN address AS a ON a.address_id = so.shipping_address_idJOIN order_item AS oi ON oi.order_id = so.order_idJOIN product AS p ON p.product_id = oi.product_idWHERE so.order_number = :order_numberORDER BY oi.line_no;

WITH line_totals AS (  SELECT order_id,         COUNT(*) AS line_count,         SUM(quantity) AS unit_count,         SUM(line_total_cents) AS order_total_cents  FROM order_item  GROUP BY order_id), payment_totals AS (  SELECT order_id,         SUM(CASE WHEN status = 'captured' THEN amount_cents ELSE 0 END) AS captured_cents,         SUM(CASE WHEN status = 'refunded' THEN amount_cents ELSE 0 END) AS refunded_cents  FROM payment  GROUP BY order_id), shipment_counts AS (  SELECT order_id, COUNT(*) AS shipment_count  FROM shipment  GROUP BY order_id), latest_shipment AS (  SELECT s.order_id, s.status AS latest_shipment_state  FROM shipment s  JOIN (    SELECT order_id, MAX(shipment_id) AS shipment_id    FROM shipment    GROUP BY order_id  ) latest    ON latest.order_id = s.order_id   AND latest.shipment_id = s.shipment_id)SELECT so.order_number,       so.status,       lt.line_count,       lt.unit_count,       lt.order_total_cents,       COALESCE(pt.captured_cents,0) AS captured_cents,       COALESCE(pt.refunded_cents,0) AS refunded_cents,       COALESCE(sc.shipment_count,0) AS shipment_count,       ls.latest_shipment_stateFROM sales_order soJOIN line_totals lt ON lt.order_id = so.order_idLEFT JOIN payment_totals pt ON pt.order_id = so.order_idLEFT JOIN shipment_counts sc ON sc.order_id = so.order_idLEFT JOIN latest_shipment ls ON ls.order_id = so.order_idORDER BY so.ordered_at, so.order_id;

SELECT  so.order_number,  so.ordered_at,  w.warehouse_code,  p.sku,  oi.quantity AS required_quantity,  i.on_hand - i.reserved AS available_quantity,  CASE    WHEN i.on_hand - i.reserved >= oi.quantity THEN 'ready'    ELSE 'short'  END AS allocation_stateFROM sales_order soJOIN order_item oi ON oi.order_id = so.order_idJOIN product p ON p.product_id = oi.product_idCROSS JOIN warehouse wLEFT JOIN inventory i  ON i.warehouse_id = w.warehouse_id AND i.product_id = oi.product_idWHERE so.status IN ('submitted','paid')ORDER BY so.ordered_at, so.order_id, oi.line_no, w.warehouse_id;

WITH order_totals AS (  SELECT order_id, SUM(line_total_cents) AS order_total_cents  FROM order_item  GROUP BY order_id), money AS (  SELECT order_id,         SUM(CASE WHEN status='captured' THEN amount_cents ELSE 0 END) AS captured_cents,         SUM(CASE WHEN status='refunded' THEN amount_cents ELSE 0 END) AS refunded_cents  FROM payment  GROUP BY order_id)SELECT so.order_number,       so.status,       ot.order_total_cents,       COALESCE(m.captured_cents,0) AS captured_cents,       COALESCE(m.refunded_cents,0) AS refunded_cents,       COALESCE(m.captured_cents,0) - COALESCE(m.refunded_cents,0)         AS net_collected_cents,       CASE         WHEN so.status='cancelled' AND COALESCE(m.refunded_cents,0)=0 THEN 'refund review'         WHEN so.status IN ('paid','packed','shipped')              AND COALESCE(m.captured_cents,0)<>ot.order_total_cents THEN 'amount mismatch'         ELSE 'ok'       END AS reconciliation_stateFROM sales_order soJOIN order_totals ot ON ot.order_id=so.order_idLEFT JOIN money m ON m.order_id=so.order_idORDER BY reconciliation_state DESC, so.order_number;

SELECT order_id, order_number, customer_id, status, ordered_atFROM sales_orderWHERE (ordered_at, order_id) < (:last_ordered_at, :last_order_id)ORDER BY ordered_at DESC, order_id DESCLIMIT :page_size;

WITH recognized AS (  SELECT so.order_id,         so.customer_id,         substr(so.ordered_at,1,7) AS order_month,         SUM(CASE               WHEN p.status='captured' THEN p.amount_cents               WHEN p.status='refunded' THEN -p.amount_cents               ELSE 0             END) AS net_revenue_cents  FROM sales_order so  JOIN payment p ON p.order_id=so.order_id  WHERE so.status <> 'cancelled'  GROUP BY so.order_id, so.customer_id, substr(so.ordered_at,1,7))SELECT r.order_month,       c.region,       COUNT(DISTINCT r.order_id) AS orders,       COUNT(DISTINCT r.customer_id) AS customers,       SUM(r.net_revenue_cents) AS net_revenue_centsFROM recognized rJOIN customer c ON c.customer_id=r.customer_idGROUP BY r.order_month, c.regionORDER BY r.order_month, c.region;

WITH product_revenue AS (  SELECT p.product_id,         p.sku,         p.product_name,         SUM(CASE WHEN so.status <> 'cancelled'                  THEN oi.quantity ELSE 0 END) AS units,         SUM(CASE WHEN so.status <> 'cancelled'                  THEN oi.line_total_cents ELSE 0 END) AS gross_cents  FROM product p  LEFT JOIN order_item oi ON oi.product_id=p.product_id  LEFT JOIN sales_order so ON so.order_id=oi.order_id  GROUP BY p.product_id, p.sku, p.product_name)SELECT sku,       product_name,       units,       gross_cents,       ROUND(100.0 * gross_cents / NULLIF(SUM(gross_cents) OVER (),0),2)         AS gross_share_pct,       DENSE_RANK() OVER (ORDER BY gross_cents DESC) AS revenue_rankFROM product_revenueORDER BY revenue_rank, sku;

WITH customer_orders AS (  SELECT so.customer_id,         so.order_id,         so.ordered_at,         SUM(oi.line_total_cents) AS order_total_cents  FROM sales_order so  JOIN order_item oi ON oi.order_id=so.order_id  WHERE so.status <> 'cancelled'  GROUP BY so.customer_id, so.order_id, so.ordered_at)SELECT c.customer_id,       c.display_name,       COUNT(co.order_id) AS completed_or_open_orders,       COALESCE(SUM(co.order_total_cents),0) AS lifetime_order_value_cents,       MIN(co.ordered_at) AS first_order_at,       MAX(co.ordered_at) AS latest_order_at,       CASE WHEN COUNT(co.order_id) >= 2 THEN 'repeat' ELSE 'single/none' END         AS customer_segmentFROM customer cLEFT JOIN customer_orders co ON co.customer_id=c.customer_idGROUP BY c.customer_id, c.display_nameORDER BY lifetime_order_value_cents DESC, c.customer_id;

SELECT  w.warehouse_code,  p.sku,  p.product_name,  i.on_hand,  i.reserved,  i.on_hand - i.reserved AS available,  i.reorder_point,  CASE    WHEN i.on_hand - i.reserved <= 0 THEN 'stockout'    WHEN i.on_hand - i.reserved <= i.reorder_point THEN 'reorder'    ELSE 'healthy'  END AS stock_stateFROM inventory iJOIN warehouse w ON w.warehouse_id=i.warehouse_idJOIN product p ON p.product_id=i.product_idWHERE p.active=1ORDER BY  CASE    WHEN i.on_hand - i.reserved <= 0 THEN 1    WHEN i.on_hand - i.reserved <= i.reorder_point THEN 2    ELSE 3  END,  available,  w.warehouse_code,  p.sku;