Product Analytics

select 
   case    when session_count = 1 then "1"
	  when session_count = 2 then "2"
	  when session_count = 3 then "3"
	  when session_count = 4 then "4"
	  when session_count = 5 then "5"
	  when session_count = 6 then "6"
	  when session_count = 7 then "7"
	  when session_count = 8 then "8"
	  when session_count > 8 and session_count < 15 then "9-14"
	  when session_count > 14 and session_count < 26 then "15-25"
	  when session_count > 25 and session_count < 51 then "26-50"
	  when session_count > 50 and session_count < 101 then "51-100"
	  when session_count > 100 and session_count < 200 then "101-200"
	  when session_count > 200  then "200+"
   end as Number_Of_Sessions
,  count(distinct user_id, exact) as Users
, max(session_count) as ordr
from (  select user_id, count(distinct session_id, exact) as session_count
  from cooladata
  where date_range(context)
  and filters(context) 
  group by 1 )
where session_count is not null
group by 1
having Users is not null and Number_Of_Sessions is not null
order by ordr 

SELECT country ,  revenues
FROM
  ( SELECT group_id ,
           CASE  WHEN group_id < 11 THEN country
               ELSE "Other"  END AS country ,
           sum(revenues) revenues
   FROM
     ( SELECT country ,  revenues ,
              if(row_number<11, row_number, 11) group_id
      FROM
        ( SELECT country , ROW_NUMBER() OVER() row_number,   revenues
         FROM
           ( SELECT ip_country as country ,  sum(amount) as revenues
            FROM cooladata 
	WHERE date_range (context) and filters (context) and event_name = ‘purchase’
            GROUP BY 1
            ORDER BY 2 DESC ) ) )
   GROUP BY 1,   2
   ORDER BY group_id ASC)

select sum(case when unique_pages =1 then 1 else 0 end) as single_page_sessions, count(*) as sessions,
(sum(case when unique_pages =1 then 1 else 0 end)/count(*))*100 as bounce_rate
from
(select session_id, count(distinct page_url) as unique_pages
from cooladata
where date_range(context)
and filters(context) 
group by session_id)

select date(event_time_ts) as day, count(distinct user_id) as DAU, count( distinct case when event_name = "purchase" and purchase_status = ‘success’ then user_id end ) as pDAU,
(count( distinct case when event_name = ‘purchase’ and purchase_status = ‘success’ then user_id end )/count(distinct user_id))*100 as payers_percentage




  from cooladata 
where slicers(context) and date_range(context)
group by day
order by day   

Select day, avg(revenue) as avg_revenue_per_player, avg_purchases_per_player
from
(Select date(event_time_ts) as day, user_id, sum(purchase_amount) as revenue, count(*) as purchases
From cooladata 
Where date_range (context)  and filters (context) and event_name = ‘purchase’ and purchase_status = ‘success’
Group by day, user_id)
Group by day

select page_path, count(distinct session_id) as sessions
from
(select  session_id, group_concat (page_url,',') as page_path
from
(select  session_id, page_url,event_time_ts  as ts
from cooladata
where date_range(context) 
and filters(context) and event_name = ‘page_load’ and page_url is not null 
order by ts)
group by 1)
group by 1
order by 2 desc
limit 20

SELECT avg(median) AS median_session_duration_mins, session_os
FROM
  (SELECT percentile_cont(0.5) OVER (PARTITION BY session_os ORDER BY p_duration) AS median, session_os
   FROM
     (SELECT path_duration() /1000/60 AS p_duration, session_os
      FROM cooladata
      WHERE date_range (doc.date_range)  
         ) )
GROUP BY session_os 

concat(lpad(string(integer(duration_seconds/3600)),2,"0"),
       ":",lpad(string(integer(duration_seconds%3600/60)),2,"0"),
       ":",lpad(string(integer(duration_seconds%60)),2,"0")) 
as avg_duration

SELECT customer_user_id, cnt1 FROM ( SELECT customer_user_id, sum(amount) AS cnt1 FROM cooladata WHERE date_range(last 7 days)  GROUP BY customer_user_id ORDER BY cnt1 DESC LIMIT 10 ) ORDER BY cnt1 DESC

select  avg(sessions)  as sessions from accounts