large_sql_data.sql

large_sql_data.sql

-- PageView Retailer By Tender Type
SELECT page_view.retailer_code                 AS "Partner", 
       CASE 
         WHEN page_view.payment_type = 'visa' 
               OR page_view.payment_type LIKE '%visa%' 
               OR page_view.payment_type = 'Visa' 
               OR page_view.payment_type = 'vi' THEN 'Visa' 
         WHEN page_view.payment_type = 'mastercard' 
               OR page_view.payment_type LIKE '%master%' 
               OR page_view.payment_type = 'mc' THEN 'MasterCard' 
         WHEN page_view.payment_type = 'discover' 
               OR page_view.payment_type LIKE '%discover%' 
               OR page_view.payment_type = 'di' THEN 'Discover' 
         WHEN page_view.payment_type = 'Amex' 
               OR page_view.payment_type = 'American Express' 
               OR page_view.payment_type LIKE '%american%' 
               OR page_view.payment_type LIKE '%amex%' 
               OR page_view.payment_type = 'ax' THEN 'Amex' 
         WHEN page_view.payment_type = 'paypal' 
               OR page_view.payment_type = 'PayPal' 
               OR page_view.payment_type LIKE '%paypal%' 
               OR page_view.payment_type = 'pp' 
               OR page_view.payment_type = 'pay pal 'THEN 'PayPal' 
         WHEN page_view.payment_type LIKE 'plcc%' 
               OR page_view.payment_type LIKE 'sak%' 
               OR page_view.payment_type LIKE 'lord%' 
               OR page_view.payment_type LIKE 'staples credit card%' 
               OR page_view.payment_type IN ( 
                  'bon-ton', 'boston store', 'younkers', 
                  'elder-beerman', 'bon-tonbon-ton' ) 
               OR page_view.payment_type LIKE 'herberger%' 
               OR page_view.payment_type LIKE 'carson%' 
               OR page_view.payment_type LIKE 'bergner%' 
               OR page_view.payment_type LIKE 'express%' 
               OR page_view.payment_type LIKE 'neiman marcus' 
               OR page_view.payment_type = 'bergdorf goodman' 
               OR page_view.payment_type = 'newegg' 
               OR page_view.payment_type LIKE 'bb%' 
               OR page_view.payment_type LIKE 'eddie%' THEN 'PLCC' 
         WHEN page_view.payment_type = 'bitcoin' THEN 'Bitcoin' 
         WHEN page_view.payment_type LIKE '%apple%' THEN 'ApplePay' 
         WHEN page_view.payment_type LIKE 'diners club' 
               OR page_view.payment_type = 'dinnersclub' THEN 'Diners Club' 
         WHEN page_view.payment_type LIKE 'japan credit%' 
               OR page_view.payment_type = 'jcb' THEN 'Japan Credit Bureau' 
         WHEN page_view.payment_type = 'giftcard' 
               OR page_view.payment_type = 'gift card' 
               OR page_view.payment_type LIKE '%gift%' THEN 'GiftCard' 
         WHEN page_view.payment_type = 'amazon' THEN 'Amazon' 
         WHEN page_view.payment_type LIKE '%union%' THEN 'UnionPay' 
         ELSE 'MalformedData' 
       END                                     AS "PaymentType", 
       Count(DISTINCT page_view.sr_browser_id) AS "Count" 
FROM   analytics.page_view_v3 AS page_view 
WHERE  page_view.auth_token IS NULL 
       AND page_view.event_date > '2017-08-25' 
       AND page_view.payment_type IS NOT NULL 
GROUP  BY 1, 
          2 
ORDER  BY 3 DESC, 
          2, 
          1 ASC