pathfinder research query

pathfinder_query.sql

select result.account_id as account_id, plan_type, ticket_create_session_w_article_view, total_tickets_created, total_articles_viewed
from (
  select account_id, sum(if(tickets_created > 0 and articles_viewed > 0, 1, 0)) ticket_create_session_w_article_view, sum(tickets_created) total_tickets_created, sum(articles_viewed) total_articles_viewed
  from (
    select 
    account_id, session, sum(if(type = 'create', 1, 0)) tickets_created, sum(if(type = 'child_view', 1, 0)) articles_viewed
    from (
      select 
      Sessionize(unix_time, user_id, 1800) as session, account_id, user_id, type
      from (
        select unix_timestamp(ts, "yyyy-MM-dd'T'HH:mm:ss+00:00") as unix_time, account_id, user_id, type
        from (
          select type, account_id, user_id, ts 
          from default.stats_hc_events 
          where dt between 20150801 and 20150807
          and user_id is not null  
          and type = 'child_view'
          union all
          select type, account_id, user_id, ts 
          from default.stats_ticket_events
          where dt between 20150801 and 20150807
          and type = 'create'
          ) x
        sort by user_id, unix_time
        ) sub_sub_query
      ) sub_query 
    group by account_id, session
    ) query
  group by account_id
  ) result
inner join classic.sharded_subscriptions ss on ss.account_id = result.account_id
where ss.account_type = 'customer'