Write Queries for Pinterest Engagement Tasks

You are given several data-manipulation tasks based on Pinterest-style product data. Use UTC for all timestamp-to-date conversions unless stated otherwise. Treat time windows as inclusive at the start and exclusive at the end.

Task 1: SQL CTR by pin format for new US users

Tables:

events

• event_id BIGINT: unique event identifier.
• user_id BIGINT: user who took the action.
• pin_id BIGINT: pin involved in the action.
• event_timestamp TIMESTAMP: time of the action in UTC.
• event_type VARCHAR: either impression or click.

users_d

• user_id BIGINT: unique user identifier.
• signup_timestamp TIMESTAMP: user signup time in UTC.
• country_code VARCHAR: ISO country code, for example US.

pins_classification

• pin_id BIGINT: unique pin identifier.
• pin_format VARCHAR: pin format, for example image, video, or idea.
• category_id BIGINT: optional content category identifier.

Relationships:

• events.user_id = users_d.user_id.
• events.pin_id = pins_classification.pin_id.

Definition:

• Click-through rate, or CTR, is number of clicks / number of impressions.
• A user is considered new for an event if the user signed up before or at the event timestamp and less than 30 days before the event timestamp.
• Only include users with country_code = US.

Write a SQL query that returns one row per event_date and pin_format with the following columns:

• event_date DATE
• pin_format VARCHAR
• impressions BIGINT
• clicks BIGINT
• ctr DOUBLE

Task 2: SQL percentage of users who saw fresh content

Tables:

pins

• pin_id BIGINT: unique pin identifier.
• created_timestamp TIMESTAMP: pin creation time in UTC.

pin_impression_events

• user_id BIGINT: user who saw the pin.
• pin_id BIGINT: pin that was impressed.
• impression_timestamp TIMESTAMP: time of the impression in UTC.
• impression_type VARCHAR: impression surface or type, such as home_feed, search, or related_pins.
• impression_count BIGINT: number of impressions represented by the row.

Definitions:

• A pin is fresh if it receives at least 2 total impressions within 7 days of created_timestamp, using the window [created_timestamp, created_timestamp + interval 7 days).
• A user saw fresh content if the user had at least one impression of a fresh pin during the reporting window.
• The denominator is distinct users with at least one impression during the reporting window.
• Use parameters :start_timestamp and :end_timestamp for the reporting window.

Write a SQL query that returns:

• total_users BIGINT
• users_who_saw_fresh_content BIGINT
• pct_users_who_saw_fresh_content DOUBLE

Task 3: Python similar pins by board co-occurrence

You are given a dictionary boards mapping each board id to the unique pins saved on that board. For a given pin, compute the similarity score between the given pin and every other pin. The similarity score is the number of boards on which the other pin appears together with the given pin.

Example input: boards = {b1: [a, c, d, e], b2: [a, c, e, f], b3: [a, c, g, i]} and given_pin = a.

Expected scores:

• c = 3
• e = 2
• d = 1
• f = 1
• g = 1
• i = 1

Do not output pins with similarity score 0. Return pins with their scores, sorted by descending score and then by ascending pin id. If an optional argument n is provided, return only the top n pins.

Task 4: Python or pandas category with highest average time spent for video pins

You are given a pandas DataFrame engagement with columns:

• user_id
• pin_id
• pin_type: for example image or video.
• category_id
• time_spent_seconds

You are also given a dictionary category_mapping from category_id to a human-readable category name. For rows whose category_id is missing from the dictionary or null, map the category to unknown.

Find the category with the highest average time_spent_seconds among video pins only. Return the category name, average time spent, and number of video-pin rows used for that category. Specify how you would handle ties.