Skip to content
SQL interview questions

AdSphere

Ad-tech / digital marketing

AdSphere runs ad campaigns for advertisers across search, social, display and video. Its event data follows the full funnel: every impression shown, every click on it, every conversion and every day's spend. Analysts compute CTR, CPC, CPA and ROAS, check budget pacing, measure frequency and attribute conversions to the right campaign, which are the core of SQL rounds at ad-tech and performance-marketing teams.

See the AdSphere tables

AdSphere schema

All tables live in the adsphere schema, so write adsphere.advertisers in your queries.

adsphere.advertisers

Companies that buy ads.

  • advertiser_id (INT) — PK
  • name (VARCHAR(80))
  • industry (VARCHAR(20)) — 'ecommerce', 'fintech', 'edtech', 'travel', 'gaming', 'fmcg'
  • country (VARCHAR(2)) — ISO code

adsphere.campaigns

Ad campaigns.

  • campaign_id (INT) — PK
  • advertiser_id (INT) — FK → advertisers.advertiser_id
  • name (VARCHAR(80))
  • channel (VARCHAR(10)) — 'search', 'social', 'display', 'video'
  • objective (VARCHAR(12)) — 'conversions', 'traffic', 'awareness'
  • start_date (DATE)
  • end_date (DATE) — nullable; NULL if open-ended
  • daily_budget (DECIMAL(12,2)) — INR
  • status (VARCHAR(8)) — 'ACTIVE', 'PAUSED', 'ENDED'

adsphere.impressions

One row per ad shown. High volume.

  • imp_id (INT) — PK
  • campaign_id (INT) — FK → campaigns.campaign_id
  • user_id (INT) — anonymous viewer id
  • shown_at (TIMESTAMP)
  • device (VARCHAR(10)) — 'mobile', 'desktop', 'tablet'
  • country (VARCHAR(2))

adsphere.clicks

Clicks on impressions.

  • click_id (INT) — PK
  • imp_id (INT) — FK → impressions.imp_id
  • campaign_id (INT) — FK → campaigns.campaign_id
  • user_id (INT)
  • clicked_at (TIMESTAMP)

adsphere.conversions

Purchases or sign-ups reported by the advertiser. Most follow a click; some are view-through (no click).

  • conv_id (INT) — PK
  • campaign_id (INT) — FK → campaigns.campaign_id
  • user_id (INT)
  • click_id (INT) — FK → clicks.click_id; nullable; NULL for view-through conversions
  • converted_at (TIMESTAMP)
  • revenue (DECIMAL(12,2)) — INR

adsphere.spend

Daily spend per campaign (INR).

  • spend_id (INT) — PK
  • campaign_id (INT) — FK → campaigns.campaign_id
  • spend_date (DATE)
  • amount (DECIMAL(12,2))

Beginner (10)

  • BeginnerActive campaignsOpen now
  • BeginnerCampaigns per channelOpen now
  • BeginnerTotal spend in the last 7 days
  • BeginnerImpressions by device (last 30 days)
  • BeginnerCampaigns that ended in the last 60 days
  • BeginnerAdvertisers by number of campaigns
  • BeginnerHigh-value conversions
  • BeginnerAverage daily budget by objective
  • BeginnerReach by country
  • BeginnerPaused campaigns and when they last spent

Intermediate (10)

  • IntermediateCTR by campaign
  • IntermediateCost per click by campaign
  • IntermediateClick-to-conversion rate by channel
  • IntermediateROAS by advertiser (last 30 days)
  • IntermediateDays a campaign overspent its budget
  • IntermediateCampaigns with impressions in the last 7 days but no clicks
  • IntermediateCost per acquisition
  • IntermediateCTR by hour of day
  • IntermediateAverage frequency per campaign
  • IntermediateTime from click to conversion

Advanced (23)

  • AdvancedFull funnel per campaign
  • AdvancedDoes CTR fall with frequency?
  • AdvancedLast-click attribution across campaigns
  • AdvancedFirst-touch vs last-touch by channel
  • AdvancedMonth-to-date budget pacing
  • AdvancedSuspicious repeated clicks
  • AdvancedWeekly ROAS by channel with week-over-week change
  • AdvancedBest campaign per advertiser by ROAS
  • AdvancedWhen did each campaign break even?
  • AdvancedView-through conversion share
  • AdvancedWilson interval for impression click-through probability
  • AdvancedDaily spend/revenue correlation without fan-out
  • AdvancedChronologically valid impression-to-conversion funnel
  • AdvancedEqual campaign credit across eligible prior clicks
  • AdvancedMissing campaign spend dates inside its scheduled run
  • AdvancedAudit denormalized click identifiers
  • AdvancedCampaigns spending without any recorded impression
  • AdvancedComposite watermark for an incremental conversion extract
  • AdvancedConversion-delay tail with a labelled valid population
  • AdvancedAdvertiser/channel spend rollups with grouping flags
  • AdvancedMedian daily impression frequency per reached user
  • AdvancedInspect an impression-existence access path
  • AdvancedMaterialized daily spend and explicit refresh

Mock interview round (5)

  • AdvancedMock round 1: Campaigns losing money
  • AdvancedMock round 2: Channel mix
  • AdvancedMock round 3: New people reached this week
  • AdvancedMock round 4: Device performance
  • AdvancedMock round 5: Daily KPI table with rolling CTR

Choose optional cookies

We use necessary cookies for account security. With your permission, we also use analytics and advertising cookies on marketing pages. See our Privacy Policy.