Calculate the 2023 Session Signup Rate
Company: Airwallex
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Given the PostgreSQL tables:
```sql
sessions (
session_id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
started_at TIMESTAMP NOT NULL
)
signups (
signup_id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
session_id BIGINT NOT NULL,
signup_at TIMESTAMP NOT NULL
)
```
For this exercise, the **2023 cohort** consists of sessions whose `started_at` is on or after `2023-01-01` and before `2024-01-01`. A cohort session converts when at least one signup row references that `session_id` and the signup timestamp is not earlier than the session start.
Write one read-only `SELECT`/CTE query that returns exactly one row with:
```text
signup_sessions, total_sessions, signup_rate
```
### Constraints and Clarifications
- The metric unit is a distinct session, not a user or raw joined row.
- Duplicate signup attempts within one session count as one converted session.
- `signup_rate` is `signup_sessions / total_sessions` as a numeric value.
- If the cohort contains no sessions, return zero counts and `NULL` for the rate.
- A signup associated with a cohort session may occur after `2023-12-31`; the cohort boundary applies to session start, not signup time.
```hint Define the denominator first
Build the exact session cohort, then use `EXISTS`, pre-aggregation, or a distinct conditional count so multiple signup rows cannot inflate either count.
```
### Evaluation Focus
- Correct half-open date filter and session-level denominator.
- Protection against one-to-many join inflation.
- The timestamp consistency condition and numeric division.
- Exact output aliases and empty-cohort behavior.
### Extension
How would you calculate user-level signup conversion when a user can have many cohort sessions?
Overview: Calculate a 2023 session signup rate in PostgreSQL with a precise cohort and denominator. Avoid duplicate conversion inflation and handle timestamp boundaries and empty cohorts.
Read the full Airwallex Data Scientist interview experience this question came from
Write one read-only SELECT/CTE query that, for sessions started on or after 2023-01-01 and before 2024-01-01, returns exactly one row with signup_sessions, total_sessions, and signup_rate. A cohort session converts when at least one signup references that session_id and signup_at is not earlier than started_at. Count distinct sessions, count duplicate signup attempts for one session once, calculate the rate as a numeric value, and for an empty cohort return zero counts and NULL. The cohort boundary applies to session start, so a later qualifying signup may occur after 2023-12-31.
Tables
sessions(session_id BIGINT, user_id BIGINT, started_at TIMESTAMP)
signups(signup_id BIGINT, user_id BIGINT, session_id BIGINT, signup_at TIMESTAMP)
Hints
- Define the denominator first: build the exact session cohort, then use EXISTS, pre-aggregation, or a distinct conditional count so multiple signup rows cannot inflate either count.