Business Problem

Google's finance team is putting together the quarterly board deck and needs a clean month-by-month revenue trend: total revenue, transaction count, and average transaction value for each calendar month, so they can spot seasonal patterns (like a Q4 spike) and flag any month that looks anomalous before the numbers go in front of leadership. The query needs to handle months with very few transactions gracefully, since a couple of early months in the dataset are sparse.

Dataset & Schema

Transactions with transaction_id, transaction_amount, transaction_date.

CREATE TABLE transactions (
  transaction_id INTEGER PRIMARY KEY,
  transaction_amount DECIMAL(10,2),
  transaction_date DATE
);

SQL Solution

SELECT 
  STRFTIME('%Y-%m', transaction_date) as month,
  COUNT(transaction_id) as transaction_count,
  ROUND(SUM(transaction_amount), 2) as total_revenue,
  ROUND(AVG(transaction_amount), 2) as average_transaction_value,
  MIN(transaction_amount) as min_transaction,
  MAX(transaction_amount) as max_transaction
FROM transactions
GROUP BY STRFTIME('%Y-%m', transaction_date)
ORDER BY month ASC;

Explanation