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

Return all May 2024 orders.
Group order revenue by month.
Calculate days between order date and shipped date.
Find sessions created in the last 30 days of a dataset.
Explain why BETWEEN can miss timestamp rows on the last day.

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. Iforder_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.