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 (partitionby country, start_date 
                                orderbycount(distinct userid) desc
                               ) as seqnum
      from table_name 
      groupby 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
 groupby itemid, start_date 
 orderby scount desc
 limit 1000) tab,
 (select itemid, country, count(*) sold_count
  from table_name
  groupby 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
    groupby1,2
)
,ranked_groups as (
    select*
    ,row_number() over (partitionby country orderby count desc)
    from items_by_country
)
select*from ranked_groups
where row_number<=1000orderby1,2,3desc
;