オンラインストアの販売ダッシュボードをSupersetで構築する#

WITH purchase_days AS (
    SELECT DISTINCT customer_id, CAST(invoicedate AS DATE) AS purchase_day
    FROM default_dataset.online_retail_ii_clean
    WHERE customer_id IS NOT NULL
      AND invoice NOT LIKE 'C%'
),
customers AS (
    SELECT
        customer_id,
        COUNT(*)          AS purchase_days,
        MIN(purchase_day) AS first_purchase,
        MAX(purchase_day) AS last_purchase
    FROM purchase_days
    GROUP BY customer_id
)
SELECT
    customer_id,
    purchase_days,
    LEAST(purchase_days, 10)                                   AS purchase_days_capped,
    CASE WHEN purchase_days >= 2 THEN 1 ELSE 0 END             AS is_repeat,
    first_purchase,
    last_purchase,
    date_diff('day', last_purchase, DATE '2011-12-09')         AS days_since_last_purchase,
    CASE
        WHEN date_diff('day', last_purchase, DATE '2011-12-09') <= 90  THEN '1. Active (90 days)'
        WHEN date_diff('day', last_purchase, DATE '2011-12-09') <= 180 THEN '2. At risk (91 to 180 days)'
        WHEN date_diff('day', last_purchase, DATE '2011-12-09') <= 365 THEN '3. Lapsed (181 to 365 days)'
        ELSE '4. Lost (over a year)'
    END                                                        AS recency_segment
FROM customers

WITH purchase_days AS (
    SELECT DISTINCT customer_id, CAST(invoicedate AS DATE) AS purchase_day
    FROM default_dataset.online_retail_ii_clean
    WHERE customer_id IS NOT NULL
      AND invoice NOT LIKE 'C%'
),
with_previous AS (
    SELECT
        customer_id,
        purchase_day,
        lag(purchase_day) OVER (PARTITION BY customer_id ORDER BY purchase_day) AS previous_purchase
    FROM purchase_days
)
SELECT
    customer_id,
    purchase_day,
    date_diff('day', previous_purchase, purchase_day) AS days_since_previous_purchase
FROM with_previous
WHERE previous_purchase IS NOT NULL

CASE country
    WHEN 'United Kingdom'       THEN 'GBR'
    WHEN 'EIRE'                 THEN 'IRL'
    WHEN 'Germany'              THEN 'DEU'
    WHEN 'France'               THEN 'FRA'
    WHEN 'Netherlands'          THEN 'NLD'
    WHEN 'Spain'                THEN 'ESP'
    WHEN 'Switzerland'          THEN 'CHE'
    WHEN 'Belgium'              THEN 'BEL'
    WHEN 'Portugal'             THEN 'PRT'
    WHEN 'Australia'            THEN 'AUS'
    WHEN 'Italy'                THEN 'ITA'
    WHEN 'Norway'               THEN 'NOR'
    WHEN 'Sweden'               THEN 'SWE'
    WHEN 'Cyprus'               THEN 'CYP'
    WHEN 'Finland'              THEN 'FIN'
    WHEN 'Austria'              THEN 'AUT'
    WHEN 'Denmark'              THEN 'DNK'
    WHEN 'Greece'               THEN 'GRC'
    WHEN 'Japan'                THEN 'JPN'
    WHEN 'USA'                  THEN 'USA'
    WHEN 'Poland'               THEN 'POL'
    WHEN 'United Arab Emirates' THEN 'ARE'
    WHEN 'Israel'               THEN 'ISR'
    WHEN 'Hong Kong'            THEN 'HKG'
    WHEN 'Singapore'            THEN 'SGP'
    WHEN 'Malta'                THEN 'MLT'
    WHEN 'Iceland'              THEN 'ISL'
    WHEN 'Canada'               THEN 'CAN'
    WHEN 'Lithuania'            THEN 'LTU'
    WHEN 'RSA'                  THEN 'ZAF'
    WHEN 'Bahrain'              THEN 'BHR'
    WHEN 'Brazil'               THEN 'BRA'
    WHEN 'Thailand'             THEN 'THA'
    WHEN 'Korea'                THEN 'KOR'
    WHEN 'Lebanon'              THEN 'LBN'
    WHEN 'Bermuda'              THEN 'BMU'
    WHEN 'Nigeria'              THEN 'NGA'
    WHEN 'Czech Republic'       THEN 'CZE'
    WHEN 'Saudi Arabia'         THEN 'SAU'
    ELSE NULL
END