ayam indonesia

ayam_co_search.sql

select items.display_name,
       pairings.display_name as paring_name,
       stats.ksi,
       stats.ksv,
       stats.kmr,
       stats.kci,
       stats.kcsv,
       stats.kcmr

from tabemiru.monthly_combination_stats stats
         inner join tabemiru.items on stats.item_id = items.id
         inner join tabemiru.items pairings on stats.pairing_item_id = pairings.id

where data_date >= '2018-01-01'

select count(distinct unique_id)
from search.search_sessions
where application_id = 6
  and data_date = '2020-03-20'
;

select start_query
     , next_query
     , count(distinct session_id) as co_sessions_count
     , max(start_query_frequency)                    as max_start_query_frequency
     , max(next_query_frequency)                     as max_next_query_frequency
     , round(co_sessions_count::float / max_next_query_frequency / log(max_next_query_frequency),
             5)                                      as co_search_rate
     , max(co_search_rate) over ()                   as max_co_search_rate
     , round(co_search_rate / max_co_search_rate, 2) as norlalized_co_search_rate
from (
         select session_id
              , query                                                                     as start_query
              , lead(query) over (partition by session_id order by time_stamp)            as next_query
              , search_frequency                                                          as start_query_frequency
              , lead(search_frequency) over (partition by session_id order by time_stamp) as next_query_frequency
         from (
                  select *
                  from (
                           select session_id
                                , started_at                       as time_stamp
                                , query
                                , sum(1) over (partition by query) as search_frequency

                           from global_dwh.search_sessions
                           where time_stamp between current_date - interval '1 month' and current_date
                             and query is not null
                       ) sessions_with_query_frequencies
                  where search_frequency > 800
              ) filtered_sessions
     ) co_sessions
where next_query is not null
  and start_query != next_query
  and start_query = 'ayam'
  and next_query like '%ayam%'

group by 1, 2

order by norlalized_co_search_rate, co_sessions_count asc

limit 1000
;

result.tsv