SQL: select top elements from groups based on count

Question

I have three tables:

1. First Block of Query: it computes the impressions

2. Second Block of Query: it computes the conversions

3. Then I am joining to create another table

---

create table stage_II_final_suzuki_1648436600 as
        select a.*,b.conversions, b.total_users 
        from tmp.stage_II_imps_suzuki_1648436600 a
        join 
        tmp.stage_II_conversions_suzuki_1648436600 b
        on a.domain = b.domain
           and a.ad_position = b.ad_position
           and a.browser_id = b.browser_id
           and a.hour_of_day = b.hour_of_day
           and a.day_of_week = b.day_of_week
           and a.exchange_id = b.exchange_id
           and a.device_type = b.device_type
        ORDER BY b.total_users;

The output is:

domain, ad_position, hour_of_day, browser_id, exchange_id, day_of_week, device_type, impressions, conversions, total users

Now, how shall I return feature values (~20%) of which caters ~80% of users.?

Features: domain, ad_position, browser_id, hour_of_day, day_of_week, exchange_id, device_type

so for an example,

browser-user share chrome - 55% Mozila FF - 17% MS Edge - 12% Safari - 15% duck duck go - 0.5% Then result should be Chrome, Mozila and Safari

and similarly for all features

Answer 1

This query uses sum(share) over (order by share desc) in a sub-query to get the running total of market share.
It includes the minimum number of browsers for the total market share to be over 80%.
NB This is tested on dbFiddle SQL server but I believe that Google bigQuery (used by google-ads-data-hub) has all the functions used.

with shares as
( select browser, share,
    sum(share) over (order by share desc) sum_shares
    from browser_user_share) ,
over80 as
( select max(share) s80
  from shares
  where sum_shares >= 80 )
select browser, share, sum_shares
from shares,
over80
where share >= over80.s80;

browser   | share | sum_shares
:-------- | ----: | ---------:
chrome    | 55.00 |      55.00
Mozila FF | 17.00 |      72.00
Safari    | 15.00 |      87.00

db<>fiddle here