SQL Interview Questions for Search and Ad Platform Roles

-- events table (used across Questions 1–3)
CREATE TABLE events (
  app_id      INTEGER,
  event_type  VARCHAR(20),  -- 'impression' or 'click'
  event_time  TIMESTAMP
);

SELECT
  app_id,
  ROUND(
    100.0 *
    SUM(CASE WHEN event_type = 'click'      THEN 1 ELSE 0 END) /
    NULLIF(SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END), 0),
  2) AS ctr
FROM events
WHERE event_time >= '2023-01-01'
  AND event_time <  '2024-01-01'
GROUP BY app_id;

-- Updated schema adds position column
CREATE TABLE ad_events (
  ad_id       INTEGER,
  position    INTEGER,   -- 1 = top slot, higher = lower on page
  event_type  VARCHAR(20),
  event_time  TIMESTAMP
);

SELECT
  position,
  SUM(CASE WHEN event_type = 'click'      THEN 1 ELSE 0 END) AS clicks,
  SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END) AS impressions,
  ROUND(
    100.0 *
    SUM(CASE WHEN event_type = 'click'      THEN 1 ELSE 0 END) /
    NULLIF(SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END), 0),
  2) AS ctr
FROM ad_events
GROUP BY position
ORDER BY position;

CREATE TABLE campaign_events (
  campaign_id INTEGER,
  ad_id       INTEGER,
  event_type  VARCHAR(20),
  event_date  DATE
);

WITH ad_ctr AS (
  SELECT
    campaign_id,
    ad_id,
    SUM(CASE WHEN event_type = 'click'      THEN 1 ELSE 0 END) AS clicks,
    SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END) AS impressions,
    ROUND(
      100.0 *
      SUM(CASE WHEN event_type = 'click'      THEN 1 ELSE 0 END) /
      NULLIF(SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END), 0),
    2) AS ctr
  FROM campaign_events
  GROUP BY campaign_id, ad_id
),
ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY ctr DESC) AS rn
  FROM ad_ctr
  WHERE impressions >= 100   -- minimum impression threshold
)
SELECT campaign_id, ad_id, clicks, impressions, ctr
FROM ranked
WHERE rn <= 3
ORDER BY campaign_id, rn;

CREATE TABLE user_events (
  user_id    INTEGER,
  query      VARCHAR(255),
  event_time TIMESTAMP
);

WITH gap_flags AS (
  SELECT
    user_id,
    query,
    event_time,
    CASE
      WHEN event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)
           > INTERVAL '30 minutes'
        THEN 1
      WHEN LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL
        THEN 1
      ELSE 0
    END AS is_new_session
  FROM user_events
),
session_numbers AS (
  SELECT
    user_id,
    query,
    event_time,
    SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
  FROM gap_flags
)
SELECT
  user_id,
  session_id,
  MIN(event_time) AS session_start,
  MAX(event_time) AS session_end,
  COUNT(*)        AS query_count
FROM session_numbers
GROUP BY user_id, session_id
ORDER BY user_id, session_id;

SELECT
  query,
  COUNT(*) AS search_count
FROM user_events
WHERE event_time >= NOW() - INTERVAL '7 days'
GROUP BY query
ORDER BY search_count DESC
LIMIT 10;

CREATE TABLE ad_clicks (
  click_id     INTEGER,
  user_id      INTEGER,
  ad_id        INTEGER,
  campaign_id  INTEGER,
  click_time   TIMESTAMP,
  revenue_usd  DECIMAL(10,2)
);

CREATE TABLE ad_impressions (
  impression_id INTEGER,
  user_id       INTEGER,
  ad_id         INTEGER,
  campaign_id   INTEGER,
  impression_time TIMESTAMP
);

WITH click_summary AS (
  SELECT
    campaign_id,
    COUNT(*)        AS total_clicks,
    SUM(revenue_usd) AS total_revenue
  FROM ad_clicks
  GROUP BY campaign_id
),
impression_summary AS (
  SELECT
    campaign_id,
    COUNT(*) AS total_impressions
  FROM ad_impressions
  GROUP BY campaign_id
)
SELECT
  c.campaign_id,
  c.total_clicks,
  i.total_impressions,
  ROUND(c.total_revenue / NULLIF(c.total_clicks, 0), 4)            AS rpc,
  ROUND(1000.0 * c.total_revenue / NULLIF(i.total_impressions, 0), 4) AS rpm
FROM click_summary c
JOIN impression_summary i USING (campaign_id)
ORDER BY rpm DESC;

WITH daily_revenue AS (
  SELECT
    campaign_id,
    DATE_TRUNC('day', click_time)::DATE AS click_date,
    SUM(revenue_usd) AS daily_rev
  FROM ad_clicks
  GROUP BY campaign_id, DATE_TRUNC('day', click_time)
)
SELECT
  campaign_id,
  click_date,
  daily_rev,
  SUM(daily_rev) OVER (
    PARTITION BY campaign_id
    ORDER BY click_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM daily_revenue
ORDER BY campaign_id, click_date;

CREATE TABLE sessions (
  session_id   INTEGER,
  user_id      INTEGER,
  channel      VARCHAR(50),   -- 'organic_search', 'paid_search', 'email', 'direct'
  created_at   TIMESTAMP,
  converted    BOOLEAN
);

WITH converted_users AS (
  SELECT DISTINCT user_id
  FROM sessions
  WHERE converted = TRUE
),
first_touch AS (
  SELECT
    s.user_id,
    s.channel,
    s.created_at,
    ROW_NUMBER() OVER (PARTITION BY s.user_id ORDER BY s.created_at) AS rn
  FROM sessions s
  INNER JOIN converted_users cu ON s.user_id = cu.user_id
)
SELECT
  user_id,
  channel AS first_touch_channel,
  created_at AS first_session_time
FROM first_touch
WHERE rn = 1
ORDER BY user_id;

CREATE TABLE search_results (
  query_id        INTEGER,
  result_id       INTEGER,
  position        INTEGER,   -- 1 = top result
  relevance_score DECIMAL(5,3),
  click_flag      BOOLEAN,
  recorded_date   DATE
);

SELECT
  query_id,
  result_id,
  position                  AS current_rank,
  relevance_score,
  RANK() OVER (
    PARTITION BY query_id
    ORDER BY relevance_score DESC
  )                         AS relevance_rank,
  position - RANK() OVER (
    PARTITION BY query_id
    ORDER BY relevance_score DESC
  )                         AS rank_displacement
FROM search_results
WHERE recorded_date = '2024-03-01'
ORDER BY query_id, current_rank;

WITH control_period AS (
  SELECT
    result_id,
    AVG(position) AS avg_pos_control
  FROM search_results
  WHERE recorded_date BETWEEN '2024-02-01' AND '2024-02-14'
  GROUP BY result_id
),
treatment_period AS (
  SELECT
    result_id,
    AVG(position) AS avg_pos_treatment
  FROM search_results
  WHERE recorded_date BETWEEN '2024-02-15' AND '2024-02-28'
  GROUP BY result_id
)
SELECT
  c.result_id,
  ROUND(c.avg_pos_control,   2) AS control_avg_pos,
  ROUND(t.avg_pos_treatment, 2) AS treatment_avg_pos,
  ROUND(t.avg_pos_treatment - c.avg_pos_control, 2) AS position_delta,
  CASE
    WHEN ABS(t.avg_pos_treatment - c.avg_pos_control) > 2
      THEN 'significant_shift'
    ELSE 'stable'
  END AS shift_flag
FROM control_period c
JOIN treatment_period t ON c.result_id = t.result_id
ORDER BY ABS(t.avg_pos_treatment - c.avg_pos_control) DESC;

CREATE TABLE click_log (
  click_id   INTEGER,
  user_id    INTEGER,
  ad_id      INTEGER,
  ip_address VARCHAR(45),
  click_time TIMESTAMP
);

WITH click_gaps AS (
  SELECT
    user_id,
    ad_id,
    click_time,
    LAG(click_time) OVER (
      PARTITION BY user_id, ad_id
      ORDER BY click_time
    ) AS prev_click_time
  FROM click_log
),
flagged_gaps AS (
  SELECT
    user_id,
    ad_id,
    click_time,
    CASE
      WHEN click_time - prev_click_time < INTERVAL '5 minutes'
        THEN 1
      ELSE 0
    END AS rapid_click
  FROM click_gaps
)
SELECT
  user_id,
  ad_id,
  COUNT(*)          AS total_clicks,
  SUM(rapid_click)  AS rapid_click_count
FROM flagged_gaps
GROUP BY user_id, ad_id
HAVING SUM(rapid_click) >= 3
ORDER BY rapid_click_count DESC;

WITH daily_clicks AS (
  SELECT
    campaign_id,
    DATE_TRUNC('day', click_time)::DATE AS click_date,
    COUNT(*) AS daily_click_count
  FROM click_log
  JOIN ad_clicks USING (click_id)
  GROUP BY campaign_id, DATE_TRUNC('day', click_time)::DATE
),
with_prior_day AS (
  SELECT
    campaign_id,
    click_date,
    daily_click_count,
    LAG(daily_click_count) OVER (
      PARTITION BY campaign_id
      ORDER BY click_date
    ) AS prior_day_clicks
  FROM daily_clicks
)
SELECT
  campaign_id,
  click_date,
  daily_click_count,
  prior_day_clicks,
  ROUND(
    100.0 * (daily_click_count - prior_day_clicks) / NULLIF(prior_day_clicks, 0),
  1) AS pct_change
FROM with_prior_day
WHERE prior_day_clicks IS NOT NULL
  AND daily_click_count < prior_day_clicks * 0.70
ORDER BY pct_change;

CREATE TABLE users (
  user_id     INTEGER,
  signup_date DATE,
  country     VARCHAR(50)
);

CREATE TABLE user_sessions (
  session_id  INTEGER,
  user_id     INTEGER,
  channel     VARCHAR(50),
  session_start TIMESTAMP
);

CREATE TABLE search_events (
  event_id    INTEGER,
  session_id  INTEGER,
  query       VARCHAR(255),
  result_clicked BOOLEAN,
  click_revenue  DECIMAL(10,2)
);

WITH session_users AS (
  SELECT DISTINCT
    u.user_id,
    u.country,
    us.session_id
  FROM users u
  JOIN user_sessions us ON u.user_id = us.user_id
),
revenue_sessions AS (
  SELECT DISTINCT
    su.user_id,
    su.country
  FROM session_users su
  JOIN search_events se ON su.session_id = se.session_id
  WHERE se.result_clicked = TRUE
    AND se.click_revenue > 0
),
country_funnel AS (
  SELECT
    su.country,
    COUNT(DISTINCT su.user_id)   AS users_with_sessions,
    COUNT(DISTINCT rs.user_id)   AS users_with_revenue
  FROM session_users su
  LEFT JOIN revenue_sessions rs ON su.user_id = rs.user_id
  GROUP BY su.country
)
SELECT
  country,
  users_with_sessions,
  users_with_revenue,
  ROUND(
    100.0 * users_with_revenue / NULLIF(users_with_sessions, 0),
  2) AS session_to_revenue_rate
FROM country_funnel
ORDER BY session_to_revenue_rate DESC;

CREATE TABLE campaigns (
  campaign_id   INTEGER,
  advertiser_id INTEGER,
  ad_id         INTEGER
);

CREATE TABLE ads (
  ad_id     INTEGER,
  region_id INTEGER
);

CREATE TABLE impressions (
  ad_id      INTEGER,
  event_time TIMESTAMP,
  is_clicked INTEGER  -- 1 for click, 0 for impression without click
);

SELECT
  c.campaign_id,
  c.advertiser_id,
  a.region_id,
  COUNT(*)                    AS total_impressions,
  SUM(i.is_clicked)           AS total_clicks,
  ROUND(AVG(i.is_clicked::FLOAT) * 100, 2) AS avg_ctr
FROM campaigns c
JOIN ads a        ON c.ad_id = a.ad_id
JOIN impressions i ON a.ad_id = i.ad_id
GROUP BY c.campaign_id, c.advertiser_id, a.region_id
ORDER BY avg_ctr DESC;

CREATE TABLE campaign_bids (
  bid_id       INTEGER,
  campaign_id  INTEGER,
  bid_time     TIMESTAMP,
  bid_price    DECIMAL(10,4)
);

WITH daily_avg_bids AS (
  SELECT
    campaign_id,
    DATE_TRUNC('day', bid_time)::DATE AS bid_date,
    AVG(bid_price) AS daily_avg_bid
  FROM campaign_bids
  GROUP BY campaign_id, DATE_TRUNC('day', bid_time)
)
SELECT
  campaign_id,
  bid_date,
  ROUND(daily_avg_bid, 4) AS daily_avg_bid,
  ROUND(
    AVG(daily_avg_bid) OVER (
      PARTITION BY campaign_id
      ORDER BY bid_date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ),
  4) AS trailing_7d_avg
FROM daily_avg_bids
ORDER BY campaign_id, bid_date;