SQL for Data Science Cheat Sheet

SELECT  region,  COUNT(*)      AS num_orders,  SUM(amount)   AS total_revenue,  AVG(amount)   AS avg_order_value,  MIN(order_date) AS first_order,  MAX(order_date) AS last_orderFROM ordersWHERE order_date >= '2024-01-01'GROUP BY regionHAVING SUM(amount) > 10000ORDER BY total_revenue DESC;

SELECT  customer_id,  order_date,  amount,  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_seq,  RANK()       OVER (ORDER BY amount DESC)                          AS amount_rank,  SUM(amount)  OVER (PARTITION BY customer_id ORDER BY order_date)  AS running_total,  LAG(amount)  OVER (PARTITION BY customer_id ORDER BY order_date)  AS prev_amountFROM orders;

-- INNER JOIN: only matching rowsSELECT o.order_id, c.customer_nameFROM orders oINNER JOIN customers c ON o.customer_id = c.customer_id;-- LEFT JOIN: all rows from orders, matched customers or NULLSELECT o.order_id, c.customer_nameFROM orders oLEFT JOIN customers c ON o.customer_id = c.customer_id;-- Common table expression (CTE)WITH monthly_sales AS (  SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total  FROM orders  GROUP BY 1)SELECT month, total FROM monthly_sales WHERE total > 50000;