MySQL Aggregation and Window Functions Cheatsheet

SELECT COUNT(*) AS total_users FROM users;

# Return the total number of rows in a table.

SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status
ORDER BY total DESC;

# Count orders by status.

SELECT SUM(total_amount) AS gross_revenue, AVG(total_amount) AS avg_order_value
FROM orders
WHERE status = 'paid';

# Calculate totals and averages.

SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 5;

# Keep only groups that meet aggregate criteria.

SELECT
  user_id,
  id AS order_id,
  created_at,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders;

# Rank a user's orders by recency.

SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY order_date) AS running_revenue
FROM daily_order_metrics;

# Compute cumulative revenue by day.

SELECT
  order_date,
  daily_revenue,
  daily_revenue - LAG(daily_revenue) OVER (ORDER BY order_date) AS delta
FROM daily_order_metrics;

# Calculate day-over-day change.

SELECT
  category,
  revenue,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM category_revenue;

# Rank categories by revenue without gaps.