Your question is Optimize Slow Query with Indexing. 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.
Explain how you would optimize a slow-running query in a big data environment using indexing or partitioning.
Use the provided schema to write a PostgreSQL query that reads only the requested event-date range, excludes internal users, aggregates valid Apple device app-open events, and reports the day-over-day change in active users for each product line.
event_date, product_line, active_users, event_count, avg_latency_ms, previous_active_users, and active_user_changeevent_date, then product_line| Column | Type | Description |
|---|---|---|
| event_idPK | BIGINT | Unique analytics event identifier |
| event_date | DATE | Event date and partition key |
| user_id | BIGINT | User associated with the event |
| device_id | BIGINT | Apple device associated with the event |
| event_name | VARCHAR(50) | Name of the recorded event |
| latency_ms | INTEGER | Observed event latency in milliseconds |
| Column | Type | Description |
|---|---|---|
| user_idPK | BIGINT | Unique user identifier |
| user_name | VARCHAR(100) | User display name |
| is_internal | BOOLEAN | Whether the user is an internal tester |
| Column | Type | Description |
|---|---|---|
| device_idPK | BIGINT | Unique device identifier |
| product_line | VARCHAR(50) | Apple device product line |
| device_model | VARCHAR(100) | Specific Apple device model |