TryBench
Data Analysis & SQL · Practitioner lesson 14 of 19

Month-by-Month Trends & SQL Dialects

Group timestamps into uniform monthly buckets using DATE_TRUNC or strftime across different SQL dialects.

Why this matters

Bosses want monthly trends, not daily noise. Grouping by month shows whether sales are growing.

Exact Dates Do Not Group

Dates like 2026-05-14 never match each other. Change each one to the 1st of its month (2026-05-01) so the same month groups together.

In real life: Sales on 3 May and 17 May are different dates, but both belong to "May". Turning them into 2026-05-01 lets SQL put them in the same bucket.

Month Buckets Differ by Database

SQLite uses strftime('%Y-%m-01', col) to get the 1st of the month. Other databases have their own function, like DATE_TRUNC.

In real life: Different countries say "thank you" in different ways, but it means the same thing. Each database has its own way to get "the month", with the same result.

Group and Sort by Month

Use the month in GROUP BY and in ORDER BY. Then the numbers run from the oldest month to the newest.

In real life: A chart with months in the wrong order is confusing. ORDER BY the month makes the line go from January to December.

Your practice task

Track monthly business revenue by grouping completed sales into uniform monthly timeline buckets. Each bucket must be named order_month and formatted to the 1st of each month ('YYYY-MM-01'). Calculate total sales as monthly_revenue, and sort the timeline chronologically from earliest to latest.

How to do it

  1. Decide how to turn every date into the 1st of its month.
  2. Total the sales for each month for completed orders.
  3. Group by the month and sort from oldest to newest.
  4. Press Run proof and read the checklist.

Practice it for free in the app. You get instant checks that show what you got right and what to fix. A lesson takes about two minutes and you can try as many times as you like.

Quick reference: Date Truncation Across SQL Dialects

Essential for cohort analysis, monthly recurring revenue (MRR), and trend dashboards.

-- PostgreSQL / Snowflake / BigQuery:
SELECT DATE_TRUNC('month', order_date) AS order_month, SUM(amount) AS monthly_revenue
FROM orders GROUP BY order_month ORDER BY order_month ASC;

-- SQLite:
SELECT strftime('%Y-%m-01', order_date) AS order_month, SUM(amount) AS monthly_revenue
FROM orders GROUP BY order_month ORDER BY order_month ASC;

-- MySQL:
SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS order_month, SUM(amount) AS monthly_revenue
FROM orders GROUP BY order_month ORDER BY order_month ASC;

-- Microsoft SQL Server:
SELECT DATETRUNC(month, order_date) AS order_month, SUM(amount) AS monthly_revenue
FROM orders GROUP BY order_month ORDER BY order_month ASC;

Practice this lesson free

More lessons in this course

See all 19 lessons in Data Analysis & SQL