• 2 tables:

• ad_clicks(ad_id, user_id, click_time, cost)
• purchases(user_id, purchase_time, purchase_value) - Calculate ROAS for each ad_id, defined as total purchase_value / total cost. ~~~sql SELECT ac.ad_id, SUM(p.purchase_value) / SUM(ac.cost) AS roas FROM ad_clicks ac JOIN purchases p ON ac.user_id = p.user_id WHERE p.purchase_time > ac.click_time GROUP BY ac.ad_id ORDER BY roas DESC;

• Table: meta_ads(ad_id, campaign_id, date, impressions, clicks, spend, conversions)
• Write a query to:
  • Compute CTR, CVR, and CPC per campaign. Be careful how we define these.
  • Return only campaigns with CTR > 2% and CPC < $1.

    select
    campaign_id,
    SUM(clicks)*1.0 / SUM(impressions) as ctr,
    SUM(conversions)*1.0 / SUM(clicks) as cvr,
    SUM(spend)*1.0 / SUM(clicks) as cpc
    from
    meta_ads
    group by
    campaign_id
    having
    SUM(clicks)*1.0 / SUM(impressions) > 0.02
    and SUM(spend)*1.0 / SUM(clicks) < 1.0
    

• users(user_id, active_status, join_time)
• posts(post_id, user_id, post_time)

  with cte as
    (
    select
   u.user_id,
   count(distinct post_id) as num_posts
    from users u
    left join posts p on u.user_id = p.user_id
    group by u.user_id
    )
  select
    num_posts,
    count(*) as num_users
  from
    cte
  group by num_posts
  

• users(user_id, user_name, join_date)
• activity(activity_id, user_id, activity_type, activity_date)
• transactions(transaction_id, user_id, amount, transaction_date)

Find users who haven’t logged in during the last 30 days.