SQL Topic collection
Date Operations & Time-Based Analytics SQL Topic exercises
Practice date arithmetic, safe timestamp ranges, calendar bucketing, dense time series, rolling windows, growth, and cohort analysis.
- Exercises
- 26
- Difficulty
- Easy to Hard
What this collection tests
Skills and query patterns
Date parts and differences, half-open intervals, calendar spines, running and moving windows, fiscal periods, sequence gaps, and cohorts
Curated exercises
Choose an exercise
Work in the live editor where supported. Advanced guided labs state their engine boundary before you open the workspace.
- Exercise 1Easy
Calculate Days Between Order and Shipping
Return each shipped order with the whole number of days from order_date to ship_date.
- Date analysis
- Type conversion
- NULL handling
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 2Easy
Extract Month and Year from Order Date
Group orders by calendar year and month and return the order count for each populated month.
- Aggregation
- Date analysis
- Type conversion
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 3Medium
Monthly Revenue Summary (Portable Month Bucketing)
Return order count and revenue by year-month across the full dataset.
- Aggregation
- Date analysis
- Numeric functions
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 4Easy
Find Active Customers in Last 30 Days
Return one row per customer with an order in the inclusive 30-day window ending on the latest order_date in the data.
- Joins
- Subqueries
- Aggregation
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 5Easy
Filter a Half-Open Timestamp Window
Return events in the half-open interval from 2024-12-01 00:00:00 inclusive to 2025-01-01 00:00:00 exclusive.
- Date analysis
- Filtering
- Sorting
SQLite + MySQL + SQL Server live · 2 guided
Open exercise → - Exercise 6Medium
Shipping SLA Breach (Slower Than 3 Days)
Return only shipped orders whose elapsed shipping time is greater than three days.
- CASE expressions
- Date analysis
- Type conversion
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 7Easy
Calculate Completed Employee Tenure Years
Calculate each employee’s completed years of service as of 2025-01-01.
- CASE expressions
- Date analysis
- Type conversion
SQLite + MySQL + SQL Server live · 2 guided
Open exercise → - Exercise 8Medium
Employee Tenure in Years and Months
Calculate each employee’s completed tenure in years and months as of 2025-01-01.
- Subqueries
- CASE expressions
- Date analysis
SQLite + MySQL + SQL Server live · 2 guided
Open exercise → - Exercise 9Hard
Rank the Busiest Transaction Hours
Aggregate orders by hour, rank hours by transaction count, and return the top two dense ranks.
- Window functions
- Subqueries
- Aggregation
SQLite + MySQL + SQL Server live · 2 guided
Open exercise → - Exercise 10Medium
Busiest Day of the Week
Aggregate orders by day of week and return its name, order count, and revenue.
- Aggregation
- Date analysis
- Numeric functions
SQLite + PostgreSQL live · 3 guided
Open exercise → - Exercise 11Medium
Weekend vs Weekday Revenue Split
Return order count, revenue, and average order value for Weekend and Weekday groups.
- Aggregation
- CASE expressions
- Date analysis
SQLite + PostgreSQL live · 3 guided
Open exercise → - Exercise 12Medium
Customer First Order, Last Order, and Lifespan
Return one row per customer with first order, last order, lifespan days, and total orders.
- Joins
- Aggregation
- Date analysis
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 13Medium
Segment Customers by Order Recency
Return customers whose latest order is more than 30 days before the dataset anchor and label them At Risk or Churned.
- Joins
- Subqueries
- Aggregation
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 14Medium
Calculate May 2024 Month-to-Date Revenue
Calculate daily revenue and month-to-date revenue for May 2024.
- Window functions
- Aggregation
- Date analysis
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 15Medium
Calculate Year-To-Date (YTD) Sales by Month
Aggregate 2024 sales by month and calculate the cumulative YTD sales within the year.
- Window functions
- Aggregation
- Date analysis
SQLite + MySQL + SQL Server live · 2 guided
Open exercise → - Exercise 16Hard
Cumulative Sum with Monthly Reset
Return each order with a cumulative monthly amount in chronological order.
- Window functions
- Aggregation
- Date analysis
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 17Hard
Build a Dense 7-Calendar-Day Moving Average
Build daily revenue for every date in calendar_days and calculate a seven-calendar-day moving average.
- Window functions
- Joins
- Subqueries
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 18Medium
Calculate a 7-Observed-Day Moving Average
Calculate daily revenue and its moving average across the current and six preceding observed order dates.
- Window functions
- Subqueries
- Aggregation
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 19Medium
Fiscal Quarter Segmentation
Aggregate revenue by fiscal quarter for fiscal year 2024, where April–June is Q1.
- Aggregation
- CASE expressions
- Date analysis
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 20Hard
Days Between Consecutive Orders per Customer
Return every order with the previous order date and day gap within that customer.
- Window functions
- Date analysis
- Type conversion
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 21Hard
Time to First Repeat Purchase
Return each repeat customer’s first two order dates and the days between them.
- Window functions
- Subqueries
- Aggregation
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 22Hard
Compare Consecutive Active Weeks
Aggregate 2024 revenue by populated week and compare each active week with the preceding active week.
- Window functions
- Subqueries
- Aggregation
SQLite live
Open exercise → - Exercise 23Hard
Quarter-over-Quarter (QoQ) Sales Growth
Aggregate 2024 revenue by calendar quarter and calculate QoQ growth from the preceding quarter.
- Window functions
- Subqueries
- Aggregation
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 24Hard
Compare Like-for-Like Year-over-Year Months
Return revenue and YoY growth for calendar months populated in both years.
- Subqueries
- Aggregation
- CASE expressions
SQLite + MySQL + SQL Server live · 2 guided
Open exercise → - Exercise 25Hard
First-Purchase Cohort Retention by Month
For each first-purchase cohort, report active customers, cohort size, and retention by months since first purchase.
- Joins
- Subqueries
- Aggregation
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 26Hard
Customer Value by First-Purchase Cohort
Calculate customer count, total revenue, and average realized revenue per customer for each first-purchase cohort.
- Joins
- Subqueries
- Aggregation
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise →
Attempt
Write a query or design from the brief before opening any hints.
Validate
Run it against the included dataset and inspect the output.
Explain
Compare the verified answer and explain each choice aloud.
Learn before practicing Date Operations & Time-Based Analytics
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
Open the interactive workspace and practice across SQL topics.