SQL question challenge (Ads conversion)

Link to Github
Here we have 2 tables

adv_info: advertiser_id | ad_id | spend: The Advertiser pay for this ad

ad_info: ad_id | user_id | price: The user spend through this ad (Assume all prices in this column >0)

DDL Scripts

  CREATE TABLE adv_info (
  advertiser_id    int,
  ad_id    int,
  spend    float
 );

 INSERT INTO adv_info VALUES (10, 200,300);
 INSERT INTO adv_info VALUES (11, 100,1000);
 INSERT INTO adv_info VALUES (13, 400,3000);
 INSERT INTO adv_info VALUES (14, 500,5000);

 CREATE TABLE ad_info (
  ad_id    int,
  user_id    int,
  price    float
 );

 INSERT INTO ad_info VALUES (200, 4,30);
 INSERT INTO ad_info VALUES (100, 4,100);
 INSERT INTO ad_info VALUES (400, 4,300);
 INSERT INTO ad_info VALUES (200, 10,31);
 INSERT INTO ad_info VALUES (100, 10,110);
 INSERT INTO ad_info VALUES (400, 10,310);

 select * 
  from ad_info;
  +-------+---------+-------+
  | ad_id | user_id | price |
  +-------+---------+-------+
  |   200 |       4 |    30 |
  |   100 |       4 |   100 |
  |   400 |       4 |   300 |
  |   200 |      10 |    31 |
  |   100 |      10 |   110 |
  |   400 |      10 |   310 |
  +-------+---------+-------+
  
 select * 
  from adv_info;
  +---------------+-------+-------+
  | advertiser_id | ad_id | spend |
  +---------------+-------+-------+
  |            10 |   200 |   300 |
  |            11 |   100 |  1000 |
  |            13 |   400 |  3000 |
  |            14 |   500 |  5000 |
  +---------------+-------+-------+
 
 

QUESTIONS

1) THE FRACTION OF ADVERTISER HAS ATLEAST 1 CONVERSION?

select cnt_adv /total coverstion_ratio 
  from (select count(distinct a.advertiser_id) cnt_adv 
    from adv_info a 
    join (select ad_id from ad_info) b 
    on a.ad_id=b.ad_id) 
    temp,
    (select count(distinct a.advertiser_id) total 
    from adv_info a
   ) tmp2;
  # Another query to get the same results
  
  select tmp1.cnv_cnt/tmp2.total_adv as fraction 
  from (select count(distinct a.advertiser_id) as cnv_cnt 
     from adv_info a join ad_info u on a.ad_id=u.ad_id
     ) tmp1 ,
     (select count(distinct advertiser_id) as total_adv 
     from adv_info
      ) 
    tmp2;
  +----------+
  | fraction |
  +----------+
  |   0.7500 |
  +----------+

2) WHAT METRICS WOULD YOU SHOW TO ADVERTISERS (ROI)? REVENUE / SPENDING

select a.advertiser_id,a.ad_id,b.revenue/a.spending roi 
 from (select advertiser_id,ad_id,sum(spend) spending 
   from adv_info 
   group by advertiser_id,ad_id
       ) a, 
       (select ad_id,sum(price) as revenue from ad_info group by ad_id) 
      b 
  where a.ad_id=b.ad_id;
  +---------------+-------+---------------------+
  | advertiser_id | ad_id | roi                 |
  +---------------+-------+---------------------+
  |            10 |   200 | 0.20333333333333334 |
  |            11 |   100 |                0.21 |
  |            13 |   400 | 0.20333333333333334 |
  +---------------+-------+---------------------+
  
  #Another query to get the same results
  select advertiser_id,ad_id,sum(spend) as sum_spend 
   from adv_info 
   group by advertiser_id,ad_id;
  +---------------+-------+-----------+
  | advertiser_id | ad_id | sum_spend |
  +---------------+-------+-----------+
  |            10 |   200 |       300 |
  |            11 |   100 |      1000 |
  |            13 |   400 |      3000 |
  +---------------+-------+-----------+

  select u.ad_id,sum(u.price) as sum_rev 
   from ad_info u 
   join adv_info a on u.ad_id=a.ad_id 
   group by u.ad_id;
  +-------+---------+
  | ad_id | sum_rev |
  +-------+---------+
  |   200 |      61 |
  |   100 |     210 |
  |   400 |     610 |
  +-------+---------+

  select tmp1.advertiser_id,tmp1.ad_id,(tmp2.sum_rev/tmp1.sum_spend)as ROI 
   from (select advertiser_id,ad_id,sum(spend) as sum_spend 
     from adv_info 
     group by advertiser_id,ad_id
     ) tmp1 
   join ( select u.ad_id,sum(u.price) as sum_rev 
     from ad_info u 
     join adv_info a on u.ad_id=a.ad_id 
     group by u.ad_id
    ) tmp2 
   on tmp1.ad_id=tmp2.ad_id;
  +---------------+-------+---------------------+
  | advertiser_id | ad_id | ROI                 |
  +---------------+-------+---------------------+
  |            10 |   200 | 0.20333333333333334 |
  |            11 |   100 |                0.21 |
  |            13 |   400 | 0.20333333333333334 |
  +---------------+-------+---------------------+