Your question is Key Alive in Time Range (SQL). Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Confluent Cloud operations needs to determine which API keys were alive during a requested interval. An API key is considered alive during the half-open interval from an ACTIVE event until the next lifecycle event, including an open-ended interval when no later event exists.
Write a PostgreSQL query that returns every registered key with at least one active interval overlapping the requested range.
LEAD to determine when each lifecycle interval ends.[start, end), so an activation exactly at the range end does not qualify.key_id and the beginning of the overlap.| Column | Type | Description |
|---|---|---|
| key_idPK | INT | Registered API key identifier |
| key_name | VARCHAR(100) | Human-readable API key name |
| owner_team | VARCHAR(100) | Owning Confluent team |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Lifecycle event identifier |
| key_id | INT | References api_keys.key_id |
| effective_at | TIMESTAMPTZ | Time the resulting state became effective |
| state_after | VARCHAR(20) | State after the event |
| event_note | TEXT | Optional operational note |
| Column | Type | Description |
|---|---|---|
| range_idPK | INT | Requested range identifier |
| range_name | VARCHAR(100) | Description of the requested range |
| range_start | TIMESTAMPTZ | Inclusive range start |
| range_end | TIMESTAMPTZ | Exclusive range end |