How do you approach SQL: Active Users Via CTE Chain?

Step 1 CTE recent_sessions: filter to the last 30 days.

What are common mistakes on SQL: Active Users Via CTE Chain?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Active Users Via CTE Chain

MediumDatabases

Description

> This problem is paper-only - see explanation for the canonical SQL solution. A CTE (Common Table Expression) lets you name an intermediate result and reference it later. CTE chains improve readability of multi-step queries. Given `Sessions(user_id, session_date)` rows (each row = one session a user had on a date), return user_ids who had **at least 3 sessions in the last 30 days** of the input. **For the auto-grader:** implement `activeUsers(sessions, asOfDate)` where `asOfDate` is the inclusive upper bound (ISO date). Look back exactly 29 days before `asOfDate` (so 30 days inclusive). Return ids sorted ascending.

Example 1

Input: sessions = [{user_id:1,session_date:"2026-04-15"},{user_id:1,session_date:"2026-04-20"},{user_id:1,session_date:"2026-05-01"},{user_id:2,session_date:"2025-01-01"}], asOfDate = "2026-05-08"

Output: [1]

Explanation: User 1 had three sessions inside the 2026-04-09..2026-05-08 window. User 2's session is older than 30 days.

Constraints

- 0 <= sessions.length <= 10^4

SQL: Active Users Via CTE Chain: approach, complexity and pitfalls

A medium sql problem, graded against 3 test cases (1 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

More SQLproblems →

If you are stuck

Show the 3-step hint ladder

Step 1 CTE `recent_sessions`: filter to the last 30 days.
Step 2 CTE `session_counts`: GROUP BY user_id, COUNT(*).
Final SELECT: filter where count >= 3.

The optimal approach

Show the approach: Canonical SQL (Postgres):

```sql

WITH recent_sessions AS (

SELECT user_id, session_date

FROM Sessions

WHERE session_date BETWEEN ($1::date - INTERVAL '29 days') AND $1::date

session_counts AS (

SELECT user_id, COUNT(*) AS n

FROM recent_sessions

GROUP BY user_id

)

SELECT user_id

FROM session_counts

WHERE n >= 3

ORDER BY user_id;

```

Why CTEs help: each step is a named, easily-reasoned-about subquery. You could collapse this into one query, but at 5+ steps the readability win is real.

Performance note: in older Postgres (<12) CTEs were optimization fences (always materialized). 12+ inlines them by default unless you use `AS MATERIALIZED`. SQL Server and MySQL inline; Oracle has its own knobs.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.