-- Evan Appel, 2018
-- This query creates a table that can then be fit into a pivot table for analysis
--    regarding day to day sales across sites and routes of purchase.

SELECT date AS date,
       site AS site,
       CASE EXTRACT (dayofweek
                     FROM date)
           WHEN 0 THEN 'SUNDAY'
           WHEN 1 THEN 'MONDAY'
           WHEN 2 THEN 'TUESDAY'
           WHEN 3 THEN 'WEDNESDAY'
           WHEN 4 THEN 'THURSDAY'
           WHEN 5 THEN 'FRIDAY'
           WHEN 6 THEN 'SATURDAY'
       END AS day ,
       SUM(phone_tx) AS phone_tx ,
       SUM(web_tx) AS web_tx ,
       SUM(total_tx) AS total_tx
FROM
  ( SELECT TRUNC(CONVERT_TIMEZONE('PST',g.transaction_time_utc)) AS date
         , w.website_short_name AS site
         , CASE
               WHEN g.source = 'ADMIN' THEN 1
               ELSE 0
           END AS phone_tx ,
           CASE
               WHEN g.source != 'ADMIN' THEN 1
               ELSE 0
           END AS web_tx ,
           1 AS total_tx
   FROM gateway.aggregate_t g
 LEFT JOIN reference.websiteidentifiers w
        ON g.website_id = w.website_id
   WHERE TRUNC(CONVERT_TIMEZONE('PST',g.transaction_time_utc)) BETWEEN CURRENT_DATE - 30 AND CURRENT_DATE
     AND (g.billing_email NOT SIMILAR TO '%(test)%'
          OR g.billing_email IS NULL)
     AND (g.first_name NOT SIMILAR TO '%(test)%'
          OR g.first_name IS NULL)
     AND (g.last_name NOT SIMILAR TO '%(test)%'
          OR g.last_name IS NULL)
     AND g.transaction_status NOT SIMILAR TO '%(Error|Declined|Void|Refund|Credit)%'
     AND g.transaction_type NOT SIMILAR TO '%(Error|Declined|Void|Refund|Credit)%'
    -- AND g.website_id = 5
   GROUP BY g.transaction_time_utc ,
            g.source, w.website_short_name)
GROUP BY date, site
ORDER BY date DESC