Calculate Average Event Value by User ID
Company: Upstart
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
events
+----+---------+------------+-------+---------------------+
| id | user_id | event_type | value | timestamp |
+----+---------+------------+-------+---------------------+
| 1 | 42 | click | 3.5 | 2024-03-10 10:00:00 |
| 2 | 17 | view | 1.0 | 2024-03-10 10:01:05 |
| 3 | 42 | purchase | 7.0 | 2024-03-10 10:02:30 |
| 4 | 99 | click | 2.3 | 2024-03-10 10:04:11 |
| 5 | 17 | purchase | 4.7 | 2024-03-10 10:05:45 |
+----+---------+------------+-------+---------------------+
##### Scenario
You receive an API response that is a Python list of JSON objects, each describing a user event coming from a mobile-app funnel.
##### Question
Write Python code that, given a list like events = [{"id":1,"user_id":42,"event_type":"click","value":3.5,"timestamp":"2024-03-10 10:00:00"}, ...], produces a dictionary mapping each user_id to the average value of their events. Return the result sorted by descending average value.
##### Hints
Use defaultdict / pandas groupby; iterate only once if possible.
Overview: This question evaluates a candidate's skill in group-wise aggregation and data transformation on JSON-like event streams, specifically computing per-user average metrics and producing ranked results.
Given the events table, write a SQL query to calculate the average value per user_id and return one row per user. The result should be sorted by the average value in descending order.
Tables
events(id INTEGER, user_id INTEGER, event_type VARCHAR(20), value DECIMAL(10,2), timestamp TIMESTAMP)
Hints
- Use AVG(value) with GROUP BY user_id.
- ORDER BY the computed average in descending order.