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 |