Dataford
Popular roles
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Browse
Browse All RolesEvery role hub, from analyst to MLBrowse All CompaniesCompany-specific interview loopsAll Interview GuidesThe full guide library
Top questions by role
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Top questions by skill
SQLPythonStatisticsMachine LearningA/B TestingSystem DesignGenerative AIProduct SenseMetricsBehavioral
Browse all questions →Try a mock interview
ExperiencesCoaching
New
Practice
Mock InterviewsTimed interview simulations with feedbackSuccess PathYour 6-week structured planModulesCurated lessons by topicWebinarsTalks from ex-Big Tech data leadsPlaygroundA free-form scratch editor
Learn
BlogInterview strategy and career adviceTech Job Market ReportHiring trends across data and AI rolesFor UniversitiesDataford for career centersAbout DatafordWho we are and how we build
Pricing
Get started
SQL: Ads Conversion Funnel
00:00
5 left

SQL: Ads Conversion Funnel

HardSQL · PostgreSQL

Problem

Meta's Product Growth team wants an AARRR Funnel view of Meta Ads performance across Instagram Reels and Facebook Groups. Write a PostgreSQL query that measures impression to click to install conversion for impressions recorded during the first week of January 2026.

Requirements

  1. Group results by campaign_id and placement.
  2. Count distinct impressions, clicks, and installs. Include groups with impressions but no clicks or installs.
  3. Attribute an install only when it occurs from the click and within seven days after that click.
  4. Calculate click-through rate, install-from-click rate, and install-from-impression rate, rounded to two decimals. Use zero when a denominator is zero.

Representative data

Output details

  • Return columns in this order: campaign_id, placement, impressions, clicks, installs, click_through_rate, install_from_click_rate, install_from_impression_rate.
  • Order rows by campaign_id ascending, then placement ascending.
  • Treat the first week as 2026-01-01 00:00:00+00 inclusive through 2026-01-08 00:00:00+00 exclusive.
  • Treat the seven-day install window as inclusive of both the click timestamp and exactly seven days afterward.
  • Render all rate values with exactly two decimal places, including trailing zeroes.

Schema

ad_impressions
ColumnTypeDescription
impression_idPKINTUnique ad impression identifier
campaign_idVARCHAR(30)Meta Ads campaign identifier
user_idVARCHAR(30)Viewer identifier
placementVARCHAR(30)Meta surface where the ad appeared
impression_atTIMESTAMPTZTimestamp of the impression
ad_clicks
ColumnTypeDescription
click_idPKINTUnique ad click identifier
impression_idINTSource impression identifier
user_idVARCHAR(30)User who clicked the ad
clicked_atTIMESTAMPTZTimestamp of the click
app_installs
ColumnTypeDescription
install_idPKINTUnique app install identifier
click_idINTAttributed click identifier
user_idVARCHAR(30)User who installed the app
installed_atTIMESTAMPTZTimestamp of the install
Tablesad_impressionsad_clicksapp_installs
Interviewer

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the three tables are in the Question tab.

You need to log in / sign up to run or submit.
CodePostgreSQL
Sign up free to run your codeLog inLn 1
Run your query to see results here.