Compute CTR by format for new US users

You are given three tables. Write a single SQL query to compute click-through rate (CTR) by pin_format for NEW users in the US, where NEW users are those whose sign_up_date is within 30 days (inclusive) of the action's event_date.

Schemas:

• events(event_date DATE, user_id BIGINT, pin_id BIGINT, event_type STRING CHECK IN ('impression','click'), event_count INT)
• users(user_id BIGINT, country STRING, sign_up_date DATE)
• pin_classification(pin_id BIGINT, pin_format STRING)

Sample data:

Table: events
event_date | user_id | pin_id | event_type | event_count
2025-08-15 | 101 | 10 | impression | 100
2025-08-15 | 101 | 10 | click | 5
2025-08-20 | 102 | 11 | impression | 50
2025-08-22 | 103 | 12 | impression | 80
2025-08-22 | 103 | 12 | click | 8
2025-08-25 | 104 | 11 | impression | 200
2025-08-25 | 104 | 11 | click | 40

Table: users
user_id | country | sign_up_date
101 | US | 2025-08-01
102 | US | 2025-07-10
103 | CA | 2025-08-10
104 | US | 2025-08-21

Table: pin_classification
pin_id | pin_format
10 | video
11 | static
12 | video

Requirements:

• Consider only events where users.country = 'US'.
• Define NEW users as 0 <= DATEDIFF(event_date, sign_up_date) <= 30.
• CTR per pin_format = SUM(click event_count) / SUM(impression event_count) across all NEW US users' events.
• Return columns: pin_format, impressions, clicks, ctr where

 impressions = SUM(CASE WHEN event_type = 'impression' THEN event_count ELSE 0 END),
 clicks = SUM(CASE WHEN event_type = 'click' THEN event_count ELSE 0 END),
 ctr = clicks / NULLIF(impressions, 0).

• Exclude pin_formats with zero impressions after filtering.
• Round ctr to 4 decimal places and order by ctr DESC; ties broken by pin_format ASC.
• Your query must be a single SELECT (CTEs allowed), handle duplicate rows safely, and not double-count events.

Compute CTR by pin_format for new US users

You are given three tables that capture pin engagement on a Pinterest-style product:

• events — one row per pin engagement event: event_date, user_id, pin_id, event_type (either 'impression' or 'click'), and event_count (how many of that event occurred). The table may contain fully duplicated rows that must not be double-counted.
• users — one row per user: user_id, country, sign_up_date.
• pin_classification — maps each pin_id to a pin_format (e.g. 'video', 'static').

Write a single PostgreSQL SELECT statement (CTEs are allowed) that computes the click-through rate (CTR) per pin_format, restricted to new users in the US.

Definitions & requirements

• A user's event is from a NEW user when 0 <= (event_date - sign_up_date) <= 30, i.e. the event happens between the sign-up day and 30 days after, inclusive. (In PostgreSQL, subtracting two DATE values yields the number of days as an integer.)
• Consider only events whose user has country = 'US'.
• Before aggregating, de-duplicate the events table so that fully identical rows are counted once.
• For each pin_format, compute:
  • impressions = SUM(event_count) over rows where event_type = 'impression',
  • clicks = SUM(event_count) over rows where event_type = 'click',
  • ctr = clicks / impressions, computed in floating/decimal arithmetic and rounded to 4 decimal places.
• Exclude any pin_format whose total impressions is 0 after filtering.
• Return exactly these columns, in this order: pin_format, impressions, clicks, ctr.
• Order the result by ctr descending; break ties by pin_format ascending.

Table events

column	type	constraints
event_date	DATE	NOT NULL
user_id	BIGINT	NOT NULL
pin_id	BIGINT	NOT NULL
event_type	VARCHAR(20)	NOT NULL, CHECK (event_type IN ('impression','click'))
event_count	INT	NOT NULL

Sample data:

event_count	event_date	event_type	pin_id	user_id
100	2025-08-15	impression	10	101
5	2025-08-15	click	10	101
50	2025-08-20	impression	11	102
80	2025-08-22	impression	12	103
8	2025-08-22	click	12	103
200	2025-08-25	impression	11	104
40	2025-08-25	click	11	104
100	2025-08-15	impression	10	101

Table users

column	type	constraints
user_id	BIGINT	PRIMARY KEY
country	VARCHAR(10)	NOT NULL
sign_up_date	DATE	NOT NULL

Sample data:

country	sign_up_date	user_id
US	2025-08-01	101
US	2025-07-10	102
CA	2025-08-10	103
US	2025-08-21	104

Table pin_classification

column	type	constraints
pin_id	BIGINT	PRIMARY KEY
pin_format	VARCHAR(50)	NOT NULL

Sample data:

pin_format	pin_id
video	10
static	11
video	12

Expected output

One row per pin_format (with non-zero impressions) for events by new US users, giving total impressions, total clicks, and CTR rounded to 4 decimals, sorted by ctr DESC then pin_format ASC.

clicks	ctr	impressions	pin_format
40	0.2000	200	static
5	0.0500	100	video

Hints

1. PostgreSQL has no DATEDIFF; subtracting two DATE values (event_date - sign_up_date) already gives an integer number of days, so use BETWEEN 0 AND 30 on that difference.
2. De-duplicate the events table first (SELECT DISTINCT over all columns) so repeated raw rows don't inflate event_count.
3. Cast one side of the CTR division to ::numeric (or use 1.0 *) so you get decimal division, and wrap the denominator in NULLIF(..., 0) to avoid divide-by-zero.