Interview practice

SQL Date Range Interview Questions

Practice date filters, half-open ranges, monthly buckets, and lead-time reports.

Date questions test whether you can filter time safely and summarize events at the right calendar grain. They are common in analyst screens because most business reports have a period: day, week, month, quarter, or cohort.

The safest default is a half-open range: include the start, exclude the next period. That pattern works for dates and timestamps without needing to guess the last second of the period.

Common date interview prompts

Answer each one yourself before you look: write your query or your reasoning in the box, or open the playground and run it. Then reveal the model answer and compare. Nothing you type is saved or sent anywhere.

Return all May 2024 orders.
Your answer Try it in the playground →
Reveal a model answer
```
select order_id, customer_id, order_date
from orders
where order_date >= date '2024-05-01'
  and order_date < date '2024-06-01'
order by order_date, order_id
```
A half-open range: on or after May 1, before June 1. It stays correct if order_date is ever a timestamp, and nobody has to remember how many days May has.
Group order revenue by month.
Your answer Try it in the playground →
Reveal a model answer
```
select date_trunc('month', o.order_date) as order_month,
       sum(oi.quantity * oi.unit_price) as revenue
from orders as o
join order_items as oi on oi.order_id = o.order_id
group by order_month
order by order_month
```
date_trunc snaps each date to the first of its month, which becomes the grouping key. Mention that a month with no orders will be missing rather than showing zero.
Calculate days between order date and shipped date.
Your answer Try it in the playground →
Reveal a model answer
```
select order_id,
       order_date,
       shipped_date,
       date_diff('day', order_date, shipped_date) as days_to_ship
from orders
where shipped_date is not null
order by days_to_ship desc, order_id
```
Unshipped orders have no shipped_date, and date math on NULL gives NULL, so filter them out or say how they should count. The function name varies: DATEDIFF in SQL Server, plain subtraction in PostgreSQL.
Find sessions created in the last 30 days of a dataset.
Your answer Try it in the playground →
Reveal a model answer
```
select session_id, session_date, channel
from sessions
where session_date > (select max(session_date) from sessions) - interval 30 day
order by session_date, session_id
```
Clarify the anchor first. 'Of a dataset' means the latest date in the data, not today, which also keeps the answer stable when the data stops updating. Say whether the window includes its start day.
Explain why BETWEEN can miss timestamp rows on the last day.
Your answer

Reveal a model answer

BETWEEN is inclusive, but a bare end date means midnight at the start of that day. Against a timestamp column, everything after 00:00:00 on the last day falls outside the range. Use >= start and < the day after the end.

Half-open range pattern

select order_id,
       customer_id,
       order_date
from orders
where order_date >= date '2024-05-01'
  and order_date < date '2024-06-01'
order by order_date, order_id

What matters

The range query checks whether you avoid inclusive end-date bugs. If order_date is a timestamp, filtering with < date '2024-06-01' keeps every instant in May and excludes June cleanly.

The bucket query checks whether you can change the report grain. Orders carry the date, line items carry the money, and the final output is one row per month.

Monthly bucket pattern

select date_trunc('month', o.order_date) as order_month,
       count(distinct o.order_id) as orders,
       round(sum(oi.quantity * oi.unit_price), 2) as booked_revenue
from orders as o
join order_items as oi on oi.order_id = o.order_id
group by order_month
order by order_month

How to talk it through

Say it plainly: "I will filter from the first day of the month up to, but not including, the first day of the next month. That keeps all times on the last day without hardcoding 23:59:59."

If the prompt says "last 30 days," clarify whether it means rolling 30 days from the current date, the latest date in the dataset, or complete calendar days. That question prevents a technically correct query from answering the wrong business period.