Calculate Average Event Value by User ID

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.

Average Event Value per User

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.

Table events

column	type	constraints
id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL
event_type	VARCHAR(20)	NOT NULL
value	DECIMAL(10,2)	NOT NULL
timestamp	TIMESTAMP	NOT NULL

Sample data:

event_type	id	timestamp	user_id	value
click	1	2024-03-10 10:00:00	42	3.5
view	2	2024-03-10 10:01:05	17	1
purchase	3	2024-03-10 10:02:30	42	7
click	4	2024-03-10 10:04:11	99	2.3
purchase	5	2024-03-10 10:05:45	17	4.7

Expected output

One row per user_id with the arithmetic mean of the value column across that user's events, sorted by avg_value in descending order.

avg_value	user_id
5.25	42
2.85	17
2.3	99

Hints

1. Use AVG(value) with GROUP BY user_id.
2. ORDER BY the computed average in descending order.
3. CAST the average to a fixed precision if you need two decimal places.