Show me all customers from California who signed up in the last 90 days and have placed at least one order

SELECT c.customer_id, c.name, c.email, c.signup_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.state = 'CA'
  AND c.signup_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY c.customer_id, c.name, c.email, c.signup_date
HAVING COUNT(o.order_id) >= 1;

Show me the top 5 products by total revenue this quarter, grouped by category

SELECT p.category, p.product_name, SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY p.category, p.product_name
ORDER BY total_revenue DESC
LIMIT 5;

Find all customers who have never placed an order

SELECT c.customer_id, c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Compare this month's total sales to the same month last year.

WITH monthly_sales AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(total_amount) AS revenue
  FROM orders
  WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 year'
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT curr.revenue AS current_revenue, prev.revenue AS previous_revenue,
  ROUND(((curr.revenue - prev.revenue) / prev.revenue) * 100, 2) AS pct_change
FROM monthly_sales curr
LEFT JOIN monthly_sales prev ON curr.month = prev.month + INTERVAL '1 year'
WHERE curr.month = DATE_TRUNC('month', CURRENT_DATE);