Solve SQL CTR and Python analytics tasks

Part A — SQL: Compute click-through rate (CTR) by pin_format for US new users. New users are those whose signup_date is within 30 days (inclusive) of the action event_date. Schema (invented):

• events(event_date DATE, user_id INT, pin_id INT, event_type STRING CHECK IN ('impression','click'), event_count INT)
• users(user_id INT, country_code STRING, signup_date DATE)
• pin_classification(pin_id INT, pin_format STRING CHECK IN ('video','static','story','other')) Notes/requirements:
• Join events→users on user_id and events→pin_classification on pin_id.
• Filter users.country_code = 'US'.
• Define new user as 0 ≤ DATEDIFF(event_date, signup_date) ≤ 30 (inclusive).
• Aggregate impressions = SUM(event_count WHERE event_type='impression'); clicks = SUM(event_count WHERE event_type='click').
• CTR = clicks / impressions, computed after aggregation at the pin_format level; exclude pin_formats whose aggregated impressions = 0 to avoid division by zero.
• Return one row per pin_format with columns: pin_format, impressions, clicks, ctr (rounded to 4 decimals), and the distinct_new_users contributing to those events.
• Ensure no double counting if multiple rows per (event_date, user_id, pin_id, event_type). ASCII sample tables:

users
+---------+--------------+-------------+
| user_id | country_code | signup_date |
+---------+--------------+-------------+
| 101 | US | 2025-07-20 |
| 102 | US | 2025-08-05 |
| 103 | CA | 2025-08-01 |
| 104 | US | 2025-06-10 |
+---------+--------------+-------------+

pin_classification
+--------+------------+
| pin_id | pin_format |
+--------+------------+
| 10 | video |
| 11 | static |
| 12 | video |
| 13 | story |
+--------+------------+

events
+------------+---------+--------+------------+-------------+
| event_date | user_id | pin_id | event_type | event_count |
+------------+---------+--------+------------+-------------+
| 2025-08-01 | 101 | 10 | impression | 5 |
| 2025-08-01 | 101 | 10 | click | 1 |
| 2025-08-10 | 102 | 11 | impression | 3 |
| 2025-08-10 | 102 | 11 | click | 0 |
| 2025-08-15 | 103 | 12 | impression | 4 |
| 2025-08-15 | 103 | 12 | click | 2 |
| 2025-08-20 | 104 | 13 | impression | 7 |
| 2025-08-20 | 104 | 13 | click | 1 |
+------------+---------+--------+------------+-------------+

Question: Write a single SQL query that produces the required output, handling all constraints above.

Part B — Python (Pandas + core Python):

1. Given a DataFrame df with columns [pin_id:int, category_id:int, time_spent_seconds:float, pin_format:str] and a dict category_map = {category_id:int -> category_name:str}, return the category_name with the highest average time_spent_seconds among rows where pin_format case-insensitively equals 'video'. Break ties by lexicographically smallest category_name. Exclude null/NaN time_spent_seconds. Aim for vectorized Pandas (no Python loops over rows).
2. Given a dict user_to_pins = {'user1':[1,2,2], 'user2':[1,2,3], ...}, compute the average number of unique pin_ids per user (i.e., mean over users of |set(pin_ids)|), returning a float. Handle empty lists, extremely large lists, and memory efficiency for millions of users. Provide time/space complexity and code.

Compute CTR by pin_format for US new users

Using the three tables below, write a single PostgreSQL query that computes the click-through rate (CTR) by pin_format, restricted to US new users.

Tables

• users — one row per user: user_id, country_code, signup_date.
• pin_classification — one row per pin: pin_id, pin_format (e.g. 'video', 'static', 'story').
• events — one row per (event_date, user_id, pin_id, event_type) bucket, with a pre-aggregated event_count. event_type is 'impression' or 'click'.

Definitions

• Join events to users on user_id, and events to pin_classification on pin_id.
• Keep only users with users.country_code = 'US'.
• A new user for a given event is one where the event happened within the user's first 30 days, i.e. 0 <= (event_date - signup_date) <= 30 (inclusive). In PostgreSQL, subtracting two DATE values yields the integer number of days, so use (event_date - signup_date) — not DATEDIFF.

Required output

Return one row per pin_format with these columns, in this order:

• pin_format
• impressions — SUM(event_count) over rows where event_type = 'impression'
• clicks — SUM(event_count) over rows where event_type = 'click'
• ctr — clicks / impressions, computed after aggregation at the pin_format level and rounded to 4 decimal places
• distinct_new_users — COUNT(DISTINCT user_id) of qualifying new users contributing events to that pin_format

Exclude any pin_format whose aggregated impressions = 0 (to avoid divide-by-zero). Aggregate event_count (do not count rows). Sort the result by pin_format ascending.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY
country_code	VARCHAR(2)	NOT NULL
signup_date	DATE	NOT NULL

Sample data:

country_code	signup_date	user_id
US	2025-07-20	101
US	2025-08-05	102
CA	2025-08-01	103
US	2025-06-10	104

Table pin_classification

column	type	constraints
pin_id	INT	PRIMARY KEY
pin_format	VARCHAR(20)	NOT NULL

Sample data:

pin_format	pin_id
video	10
static	11
video	12
story	13

Table events

column	type	constraints
event_date	DATE	NOT NULL
user_id	INT	NOT NULL
pin_id	INT	NOT NULL
event_type	VARCHAR(20)	NOT NULL
event_count	INT	NOT NULL

Sample data:

event_count	event_date	event_type	pin_id	user_id
5	2025-08-01	impression	10	101
1	2025-08-01	click	10	101
3	2025-08-10	impression	11	102
0	2025-08-10	click	11	102
4	2025-08-15	impression	12	103
2	2025-08-15	click	12	103
7	2025-08-20	impression	13	104
1	2025-08-20	click	13	104

Expected output

One row per pin_format for US new users (event within 0-30 days of signup), with total impressions, total clicks, CTR (clicks/impressions rounded to 4 decimals), and distinct contributing new-user count; pin_formats with zero impressions excluded; ordered by pin_format.

clicks	ctr	distinct_new_users	impressions	pin_format
0	0.0000	1	3	static
1	0.2000	1	5	video

Hints

1. In PostgreSQL, subtracting two DATE values gives an integer number of days, so use (event_date - signup_date) instead of DATEDIFF for the 0-30 day window.
2. Use SUM(CASE WHEN event_type = ... THEN event_count ELSE 0 END) to total impressions and clicks per pin_format in one pass.
3. Cast the numerator to numeric (clicks::numeric) before dividing and wrap the denominator in NULLIF(..., 0) so the division is non-integer and divide-by-zero safe.