justgig8.sql

select MAX(workflow_id) as workflow_id,
     MAX(action_id) as action_id,
     attempt_id,
     MAX(product_id) as product_id,
     MAX(‘NA’) as offer_id,
     MAX(customer_id) as customer_id,
     MAX(channel) as channel,
     MAX(channel_id) as channel_id,
     MAX(case when metric=‘attempted’ then ‘Yes’ else ‘No’ end) as attempted,
     MIN(case when metric=‘attempted’ then DATE_FORMAT(‘%Y-%m-%d %H:%i:%s’, ‘UTC’, created_at ) else ‘NA’ end) as attempt_time,
     MAX(case when metric=‘delivered’ then ‘Yes’ else ‘No’ end) as delivered,
     MIN(case when metric=‘delivered’ then DATE_FORMAT(‘%Y-%m-%d %H:%i:%s’, ‘UTC’, created_at ) else ‘NA’ end) as delivered_time,
     SUM(case when metric=‘opened’ then 1 else 0 end) as opened_count,
     IF(
          MAX(case when metric=‘opened’ then DATE_FORMAT(‘%Y-%m-%d %H:%i:%s’, ‘UTC’, created_at ) else ‘0’ end)=0,
          ‘NA’,
          MAX(case when metric=‘opened’ then DATE_FORMAT(‘%Y-%m-%d %H:%i:%s’, ‘UTC’, created_at ) else ‘0’ end)) as last_open_time,
     SUM(case when metric=‘clicked’ then 1 else 0 end) as clicked_count,
     IF(
          MAX(case when metric=‘clicked’ then DATE_FORMAT(‘%Y-%m-%d %H:%i:%s’, ‘UTC’, created_at ) else ‘0’ end)=0,
          ‘NA’,
          MAX(case when metric=‘clicked’ then DATE_FORMAT(‘%Y-%m-%d %H:%i:%s’, ‘UTC’, created_at ) else ‘0’ end)) as last_click_time,
     MAX(case when metric=‘spammed’ then ‘Yes’ else ‘No’ end) as spammed,
     IF(
          MAX(case when metric=‘spammed’ then DATE_FORMAT(‘%Y-%m-%d %H:%i:%s’, ‘UTC’, created_at ) else ‘0’ end)=0,
          ‘NA’,
          MAX(case when metric=‘spammed’ then DATE_FORMAT(‘%Y-%m-%d %H:%i:%s’, ‘UTC’, created_at ) else ‘0’ end)) as last_spam_time,
     MAX(case when metric=‘unsubscribed’ then ‘Yes’ else ‘No’ end) as unsubscribed,
     IF(
          MAX(case when metric=‘unsubscribed’ then DATE_FORMAT(‘%Y-%m-%d %H:%i:%s’, ‘UTC’, created_at ) else ‘0’ end)=0, 
          ‘NA’,
          MAX(case when metric=‘unsubscribed’ then DATE_FORMAT(‘%Y-%m-%d %H:%i:%s’, ‘UTC’, created_at ) else ‘0’ end)) as last_unsubscription_time,
     MAX(case when metric in (‘attemptfailed’,‘deliveryfailed’) then ‘Yes’ else ‘No’ end) as failed,  
     IF(
          MAX(case when metric in (‘attemptfailed’,‘deliveryfailed’) then description else ‘A’ end)=‘A’,
          ‘NA’,
          MAX(case when metric in (‘attemptfailed’,‘deliveryfailed’) then description else ‘A’ end)) as failure_reason, 
     IF(
          MAX(case when metric in (‘attemptfailed’,‘deliveryfailed’) then DATE_FORMAT(‘%Y-%m-%d %H:%i:%s’, ‘UTC’, created_at ) else ‘0’ end)=0,
          ‘NA’,
          MAX(case when metric in (‘attemptfailed’,‘deliveryfailed’) then DATE_FORMAT(‘%Y-%m-%d %H:%i:%s’, ‘UTC’, created_at ) else ‘0’ end)) as last_failure_time,
     MAX(case when metric=‘converted’ then ‘Yes’ else ‘No’ end) as converted,
     IF(
          MAX(case when metric=‘converted’ then DATE_FORMAT(‘%Y-%m-%d %H:%i:%s’, ‘UTC’, created_at ) else ‘0’ end)=0,
          ‘NA’,
          MAX(case when metric=‘converted’ then DATE_FORMAT(‘%Y-%m-%d %H:%i:%s’, ‘UTC’, created_at ) else ‘0’ end)) as last_conversion_update_time,
     MAX(‘NA’) as Zone,
     MAX(‘NA’) as Region,
     MAX(‘NA’) as Branch,
     MAX(‘NA’) as State,
     MAX(‘NA’) as Country,
     MAX(‘NA’) as Age,
     MAX(‘NA’) as Gender,
     MAX(‘NA’) as ip_address,
     MAX(‘NA’) as latitude,
     MAX(‘NA’) as longitude,
     MAX(‘NA’) as device,
     MAX(‘NA’) as manufacturer,
     MAX(‘NA’) as os,
     MAX(‘NA’) as browser,
     MAX(‘NA’) as browser_engine,
     MAX(‘NA’) as loc_city,
     MAX(‘NA’) as loc_state,
     MAX(‘NA’) as loc_country 
from reporting_engagements 
where workflow_id=159 
     and created_on in (‘2017-10-18’,‘2017-10-19’,‘2017-10-20’,‘2017-10-21’,‘2017-10-22’)
group by attempt_id
limit 5000000