Date, Time & Cohort SQL Queries: Interview Patterns You Can Reuse

At 11:58 pm, a customer places an order during a fashion sale; at 12:03 am, the warehouse scans it. One dashboard counts it in yesterday's sale, another counts it today, and suddenly the business is arguing over SQL, not strategy.

Big Picture: Date SQL Turns Raw Events Into Business Time

A timestamp is just a recorded moment. Analytics begins when you translate that moment into the business calendar, attach it to a user or account, and compare behaviour across time.

The Five Query Patterns Interviewers Reuse

Most SQL interview questions on dates and cohorts are not new questions. They are variations of five patterns. Once you recognise the pattern, the query becomes much easier to structure.

The Core SQL Building Blocks

1. Pick the right timestamp. For revenue, payment success may matter. For logistics, delivery timestamp may matter. For product activation, first login or first transaction may matter. A wrong timestamp creates a clean-looking but wrong answer.

2. Convert timestamp to reporting grain. A business may report by IST day, financial week, calendar month or fiscal quarter. The SQL function differs by database, but the thinking is the same: convert fine-grained time into the reporting bucket.

3. Build cohorts from the first qualifying event. A cohort is a group of users sharing a starting condition, such as first order month, signup week or first subscription date. The keyword is first.

A typical monthly retention query has this shape:

WITH first_event AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(order_ts)) AS cohort_month
  FROM orders
  GROUP BY user_id
),
activity AS (
  SELECT
    user_id,
    DATE_TRUNC('month', order_ts) AS activity_month
  FROM orders
  GROUP BY user_id, DATE_TRUNC('month', order_ts)
),
cohort_size AS (
  SELECT cohort_month, COUNT(*) AS users_in_cohort
  FROM first_event
  GROUP BY cohort_month
)
SELECT
  f.cohort_month,
  a.activity_month,
  /* month_number syntax differs by database */
  COUNT(DISTINCT a.user_id) AS active_users,
  c.users_in_cohort,
  COUNT(DISTINCT a.user_id) * 1.0 / c.users_in_cohort AS retention_rate
FROM first_event f
JOIN activity a ON f.user_id = a.user_id
JOIN cohort_size c ON f.cohort_month = c.cohort_month
GROUP BY f.cohort_month, a.activity_month, c.users_in_cohort
ORDER BY f.cohort_month, a.activity_month;

4. Use window functions after aggregation. For a 7-day rolling revenue question, first create one row per day. Then apply `AVG`, `SUM` or `COUNT` over the ordered daily rows. If you run a window directly on raw transactions, high-transaction days distort the result.

5. Label the period age. Cohort tables are easiest to read when columns are Month 0, Month 1, Month 2 rather than January, February, March. Month 0 means the same month as the first event; Month 1 means one month later.

Worked Example: Monthly Cohort Retention

Suppose an e-commerce app has a January first-order cohort of 1,000 users. In January, all 1,000 are counted in Month 0. In February, 300 of those same users order again. In March, 220 of the original January users order again.

The denominator stays fixed at 1,000 because retention asks: “What share of the original cohort came back?” If you divide 220 by 300, you are measuring survival from Month 1 to Month 2, not Month 2 retention from acquisition.

Metrics to Track in Cohort Queries

Cohort queries become powerful when the metric is defined clearly. Do not say “engagement improved” without naming the measure.

Definitions You Should Be Able to Say Cleanly

Case Study: Myntra EORS and Cohort Discipline

Situation. A large fashion sale creates a visible spike in sessions, orders and dispatches. But a spike alone does not answer the important business question: did the sale acquire durable customers, or did it only pull forward one-time bargain purchases?

The move. The right analysis separates three time ideas. First, it fixes the sale window using the correct Indian business date, not just raw server time. Second, it cohorts customers by first purchase month or first EORS purchase. Third, it tracks Month 1, Month 2 and category repeat behaviour after the sale.

Outcome or lesson. The primary driver of useful insight is the cohort denominator - first-time sale buyers must be separated from existing loyal customers. Supporting drivers include clean timestamp selection, category-level repeat tracking, return and cancellation treatment, and channel segmentation such as app, web, affiliate or paid acquisition. The strategic “so what” is simple: revenue during the sale is only half the story; retention after the sale tells whether discounts built a customer base.

How AI Changes Date, Time & Cohort Queries

1. Natural-language SQL is faster, but grain mistakes remain human. Tools can draft a cohort query from “monthly retention by first order,” but you must still specify the timestamp, time zone, cohort event and denominator. AI accelerates syntax; it does not automatically understand business definitions.

2. AI helps detect cohort anomalies. In 2026 analytics workflows, ML-based monitoring can flag a sudden fall in Month 1 retention, an unusual weekend revenue pattern or a timezone-related reporting break after a data pipeline change. The analyst still investigates whether the change is product, marketing, seasonality or data quality.

3. Semantic layers become more important. As teams use AI copilots to ask data questions, companies need certified definitions for “active user,” “first order,” “net revenue,” “business date” and “retention.” Without that, different AI-generated SQL answers will look plausible but disagree.

Interview Relevance

Common Mistake

What to Revise Next

Once you can write date and cohort queries, move to performance and pattern fluency. These topics help you explain not only the correct SQL, but also why it will run efficiently on real data.