Find Top 1000 Entries Along With Count And Rank From Table

I have a table with around 30 billions rows in Redshift with following structure, userid itemid country start_date uid1 itemid1 country1 2018-07-25 00:00:00 uid2 i

Solution 1:

If I assume that "version" means "country", then I think you want:

select *
from (select itemid, country, start_date, count(distinct userid) as num_users,
             row_number() over (partition by country, start_date 
                                order by count(distinct userid) desc
                               ) as seqnum
      from table_name 
      group by item_id, country, start_date
     ) x
where seqnum <= 1000

Solution 2:

 select itemid, country, sold_count, start_date
 from (select itemid, start_date, count(*) as scount
 from table_name
 group by itemid, start_date 
 order by scount desc
 limit 1000) tab,
 (select itemid, country, count(*) sold_count
  from table_name
  group by itemid, country) tab1
  where tab.itemid = tab1.itemid

Solution 3:

as it says in your question, you want "to find item's are bought by how many unique users and then pick top 1000 most sold item for each country and start_date", so you can try to do exactly this step by step with CTEs, instead of writing a single query:

with 
 items_by_country as (
    select 
     itemid
    ,country
    ,count(distinct userid)
    ,min(start_date) as start_date
    from table_name
    group by 1,2
)
,ranked_groups as (
    select 
     *
    ,row_number() over (partition by country order by count desc)
    from items_by_country
)
select *
from ranked_groups
where row_number<=1000
order by 1,2,3 desc
;