SQL to find dominant page visited by most users in a given time-range by medium?

azaveri7 0
Viewed 25

I need to identify that for a particular interval of time, which are the top 3 pages visited and their total visits by all the users who performed a particular event called 'Checkout' based on medium.

The table name is event_table in AWS athena and the columns are user_id, medium, event_name, event_date.

I have written the following sql to populated all the pages visited by a user based on medium and total visits.

select medium, page_path, count(page_path)
from events_table 
where date_parse(event_date,'%Y-%m-%d') >= date_parse('2022-09-16','%Y-%m-%d')
  and date_parse(event_date,'%Y-%m-%d') <= date_parse('2022-09-22','%Y-%m-%d')
  and event_name = 'Checkout'
  and user_id = '002300-166240'
group by 1, 2
order by 2 desc

How to extend above sql to list out top 3 most visited pages based on medium by considering all the users?

Sample data:

medium  page_path                     _col2
Traffic /collectibles/all               168
Traffic /collectibles/home              2
Traffic /products/multi-config          5
Traffic /products/diamond-ring          1
google  /products/solid-original        6
Traffic /collectibles/shop              1
google  /collectibles/all-hairdryers    85
amazon-athena presto sql
1 Answers

You can use row_number (or rank or dense_rank depending on requirements for handling items with the same count) window function with partition and ordering:

-- sample data
WITH dataset(medium, page_path, cnt) AS (
 values ('Traffic' ,'/collectibles/all',               168),
    ('Traffic' ,'/collectibles/home',              2),
    ('Traffic' ,'/products/multi-config',         5),
    ('Traffic' ,'/products/diamond-ring',         1),
    ('google' ,'/products/solid-original',        6),
    ('Traffic' ,'/collectibles/shop',              1),
    ('google' ,'/collectibles/all-hairdryers',    85)
)

-- query
select medium, page_path, cnt
from (
    select *,
           row_number() over (partition by medium order by cnt desc) ord
    from dataset)
where ord <= 3;

Output:

medium page_path cnt
Traffic /collectibles/all 168
Traffic /products/multi-config 5
Traffic /collectibles/home 2
google /collectibles/all-hairdryers 85
google /products/solid-original 6
Guru Stron0
Related