Meta-style Company ChallengeMediumVerified answerSQLite live

Ad Campaign Performance

For each ad campaign with at least 1,000 impressions, what is the click-through rate (CTR) and the conversion rate from clicks?

  • Joins
  • Numeric functions
  • NULL handling
  • Filtering
  • Sorting

Challenge brief

Understand the request

Ads Performance Team is preparing a campaign effectiveness report for advertisers and needs CTR and conversion metrics for all qualifying campaigns.

Calculate CTR% and conversion rate% for ads with at least 1000 impressions, joined to their page name.

Return

  • ad_title
  • page_name
  • impressions
  • clicks
  • conversions
  • ctr_percentage (clicks/impressions*100)
  • conversion_rate (conversions/clicks*100)

Constraints

  • Return campaigns with at least 1,000 impressions
  • Conversion rate must remain defined safely when a campaign has zero clicks
  • Show the highest conversion rates first

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

ads

  • ad_idINTEGER
  • page_idINTEGER
  • ad_titleVARCHAR(200)
  • impressionsINTEGER
  • clicksINTEGER
  • conversionsINTEGER

pages

  • page_idINTEGER
  • page_nameVARCHAR(100)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Ad data is in ads, page name is in pages. CTR = clicks / impressions * 100. Conversion rate = conversions / clicks * 100. Use NULLIF(clicks, 0) as the denominator for conversion rate to avoid division by zero.

Hint 2

INNER JOIN ads to pages on page_id. WHERE a.impressions >= 1000. ROUND(a.clicks * 100.0 / a.impressions, 2) AS ctr_percentage. ROUND(a.conversions * 100.0 / NULLIF(a.clicks, 0), 2) AS conversion_rate.

Hint 3

Start with the tables that establish the result grain for question 11, select the required output aliases, and add the remaining joins, filters, aggregation, and ordering one clause at a time.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT a.ad_title, p.page_name, a.impressions, a.clicks, a.conversions, ROUND((a.clicks * 100.0 / a.impressions), 2) AS ctr_percentage, ROUND((a.conversions * 100.0 / NULLIF(a.clicks, 0)), 2) AS conversion_rate FROM ads a INNER JOIN pages p ON a.page_id = p.page_id WHERE a.impressions >= 1000 ORDER BY conversion_rate DESC, a.ad_title ASC;

Why this works

Multiplying by 100.0 (not 100) forces floating-point division in SQLite. NULLIF(clicks, 0) returns NULL when clicks = 0, preventing a crash. All 3 ads have >= 1000 impressions so all qualify.

Success check

3 ads — Design Course leads at 12.5% conversion, AI Workshop at 10.67%, Tech Gadgets at 10%

Expected result

Use this output to verify values, aliases, ordering, and row count.

ad_titlepage_nameimpressionsclicksconversionsctr_percentageconversion_rate
Design CourseDesign Inspiration500020025412.5
AI WorkshopAI Research Hub1500075080510.67
Latest Tech GadgetsTech News Daily10000450454.510

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.