Data Coding

Which ads pay for themselves?

HardPinterestdata qualitynot existsreturn on ad spendntilesizing· 160 XP (40 per step)

Mock interview. You're interviewing for a senior ads analyst job on a visual discovery app. The interviewer says: "Advertisers pay for every click. We want to show them which campaigns earn their money back, and suggest where to move budget."

  • ad_events has one row per impression, click or checkout. Checkout rows carry the revenue the advertiser made.
  • Each campaign pays its cost_per_click for every valid click.
  • Return on ad spend (ROAS) = revenue / spend. A ROAS of 3 means $3 back for every $1 spent.

Step 1: Check the data first

"Before any money maths: a click should always come after the person saw that campaign's ad. List clicks with no impression from the same user and campaign at or before the click time."

Expected output

  • Columns event_id, campaign_id, user_id, event_ts; sorted by event_id

Example of the shape (made-up values):

event_id campaign_id user_id event_ts
1234 5 99 2026-04-02 10:00:00

Hints, the later steps and the answer come with Pro.

Step 1 of 4

Write your query here. Ctrl + Enter submits.