Your question is Efficient Session Features in BigQuery. Start with the requirements and the two 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.
How would you handle and aggregate millions of transactional records in Google BigQuery to extract user session features efficiently?
Write a PostgreSQL query that produces one feature row per qualifying user session. Include only completed transactions from January 2025, then enrich the results with user attributes.
user_id, session_id, user_name, country, platform, event_count, total_amount, duration_seconds, and distinct_event_types.user_id ascending, then session_id ascending.| Column | Type | Description |
|---|---|---|
| user_idPK | INTEGER | Unique user identifier |
| user_name | VARCHAR(100) | Display name of the user |
| country | VARCHAR(50) | User country |
| platform | VARCHAR(20) | Primary betting platform |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INTEGER | Unique transaction event identifier |
| user_id | INTEGER | User associated with the transaction |
| session_id | VARCHAR(30) | Application session identifier |
| event_ts | TIMESTAMP | Timestamp when the event occurred |
| event_type | VARCHAR(30) | Type of user transaction event |
| amount | NUMERIC(12,2) | monetary amount associated with the event |
| status | VARCHAR(20) | Processing status of the transaction |