Luxury Automobile Audience

SELECT
    hem,
    maids,
    signal_strength,
    sustained_intent_days,
    secondary_topic_count,
    brand_interest_daily,
    state,
    city,
    age_range,
    income_range,
    net_worth
FROM
    signal_products.luxury_automobile_audience
WHERE
    intent_date = (
        SELECT MAX(intent_date)
        FROM signal_products.luxury_automobile_audience
    )
    AND signal_strength >= 0.7
    AND sustained_intent_days >= 3
ORDER BY
    signal_strength DESC
LIMIT 1000;

SELECT
    state,
    COUNT(*)                                AS total_intenders,
    SUM(mercedes_benz_intent::INT)          AS mercedes_benz,
    SUM(bmw_intent::INT)                    AS bmw,
    SUM(audi_intent::INT)                   AS audi,
    SUM(tesla_intent::INT)                  AS tesla,
    SUM(porsche_intent::INT)                AS porsche,
    SUM(land_rover_intent::INT)             AS land_rover,
    SUM(lexus_intent::INT)                  AS lexus,
    SUM(volvo_intent::INT)                  AS volvo,
    SUM(cadillac_intent::INT)               AS cadillac,
    SUM(lincoln_intent::INT)                AS lincoln,
    SUM(ferrari_intent::INT)                AS ferrari,
    SUM(lamborghini_intent::INT)            AS lamborghini,
    SUM(jaguar_intent::INT)                 AS jaguar,
    SUM(rolls_royce_intent::INT)            AS rolls_royce,
    SUM(aston_martin_intent::INT)           AS aston_martin,
    SUM(bentley_intent::INT)                AS bentley,
    SUM(maserati_intent::INT)               AS maserati,
    SUM(mclaren_intent::INT)                AS mclaren,
    SUM(bugatti_intent::INT)                AS bugatti
FROM
    signal_products.luxury_automobile_audience
WHERE
    intent_date = (
        SELECT MAX(intent_date)
        FROM signal_products.luxury_automobile_audience
    )
    AND state IS NOT NULL
GROUP BY
    state
HAVING
    total_intenders >= 50
ORDER BY
    total_intenders DESC;

WITH recent AS (
    SELECT
        hem,
        intent_date,
        signal_strength,
        LAG(signal_strength) OVER (
            PARTITION BY hem
            ORDER BY intent_date
        ) AS prev_strength
    FROM
        signal_products.luxury_automobile_audience
    WHERE
        intent_date >= DATEADD(
            DAY,
            -7,
            (SELECT MAX(intent_date) FROM signal_products.luxury_automobile_audience)
        )
)

SELECT
    hem,
    COUNT(*)                                                        AS days_active,
    MIN(signal_strength)                                            AS earliest_strength,
    MAX(signal_strength)                                            AS latest_strength,
    MAX(signal_strength) - MIN(signal_strength)                     AS strength_change,
    SUM(CASE WHEN signal_strength > prev_strength THEN 1 ELSE 0 END) AS days_increasing
FROM
    recent
GROUP BY
    hem
HAVING
    days_active >= 3
    AND strength_change > 0
ORDER BY
    strength_change DESC
LIMIT 500;

SELECT
    age_range,
    gender,
    income_range,
    net_worth,
    COUNT(*)                                                            AS intender_count,
    ROUND(AVG(signal_strength), 3)                                      AS avg_signal_strength,
    ROUND(AVG(sustained_intent_days), 1)                                AS avg_sustained_days,
    ROUND(
        SUM(CASE WHEN secondary_topic_count > 0 THEN 1 ELSE 0 END)
        / COUNT(*),
        3
    )                                                                   AS pct_with_secondary_signals,
    SUM(CASE WHEN homeowner = 'true' THEN 1 ELSE 0 END)                AS homeowner_count
FROM
    signal_products.luxury_automobile_audience
WHERE
    intent_date = (
        SELECT MAX(intent_date)
        FROM signal_products.luxury_automobile_audience
    )
    AND brand_interest_daily = 'Multi-Brand'
    AND age_range IS NOT NULL
    AND gender IS NOT NULL
GROUP BY
    age_range,
    gender,
    income_range,
    net_worth
HAVING
    intender_count >= 10
ORDER BY
    intender_count DESC;

SELECT
    intent_date,
    COUNT(DISTINCT hem)                                                     AS unique_intenders,
    ROUND(AVG(signal_strength), 4)                                          AS avg_signal_strength,
    ROUND(AVG(sustained_intent_days), 2)                                    AS avg_sustained_days,
    SUM(CASE WHEN brand_interest_daily = 'Multi-Brand' THEN 1 ELSE 0 END)  AS multi_brand_count,
    ROUND(
        SUM(CASE WHEN brand_interest_daily = 'Multi-Brand' THEN 1 ELSE 0 END)
        / COUNT(*),
        3
    )                                                                       AS multi_brand_pct,
    SUM(CASE WHEN secondary_topic_count > 0 THEN 1 ELSE 0 END)             AS with_secondary_signals,
    SUM(CASE WHEN sustained_intent_days >= 3 THEN 1 ELSE 0 END)            AS sustained_3_plus_days
FROM
    signal_products.luxury_automobile_audience
GROUP BY
    intent_date
ORDER BY
    intent_date;

SELECT
    state,
    city,
    zip,
    COUNT(DISTINCT hem)                                                     AS unique_intenders,
    ROUND(AVG(signal_strength), 3)                                          AS avg_signal_strength,
    SUM(CASE WHEN sustained_intent_days >= 3 THEN 1 ELSE 0 END)            AS sustained_intenders,
    SUM(CASE WHEN brand_interest_daily = 'Multi-Brand' THEN 1 ELSE 0 END)  AS multi_brand_intenders,
    SUM(CASE WHEN homeowner = 'true' THEN 1 ELSE 0 END)                    AS homeowners,
    SUM(tesla_intent::INT)                                                  AS tesla,
    SUM(bmw_intent::INT)                                                    AS bmw,
    SUM(mercedes_benz_intent::INT)                                          AS mercedes_benz
FROM
    signal_products.luxury_automobile_audience
WHERE
    intent_date = (
        SELECT MAX(intent_date)
        FROM signal_products.luxury_automobile_audience
    )
    AND zip IS NOT NULL
GROUP BY
    state,
    city,
    zip
HAVING
    unique_intenders >= 10
ORDER BY
    unique_intenders DESC
LIMIT 200;

SELECT
    a.HEM,
    m.VALUE::STRING                                                     AS maid,
    a.SIGNAL_STRENGTH,
    a.BRAND_INTEREST_DAILY,
    a.STATE,
    a.AGE_RANGE,
    a.INCOME_RANGE
FROM SIGNAL_PRODUCTS.LUXURY_AUTOMOBILE_AUDIENCE a,
     LATERAL FLATTEN(INPUT => a.MAIDS) m
WHERE a.INTENT_DATE = (SELECT MAX(INTENT_DATE) FROM SIGNAL_PRODUCTS.LUXURY_AUTOMOBILE_AUDIENCE)
  AND a.SIGNAL_STRENGTH >= 0.6
  AND a.MAIDS IS NOT NULL
ORDER BY a.SIGNAL_STRENGTH DESC
LIMIT 5000;

SELECT
    HEM,
    MAIDS,
    SIGNAL_STRENGTH,
    SUSTAINED_INTENT_DAYS,
    STATE,
    CITY,
    ZIP,
    AGE_RANGE,
    INCOME_RANGE,
    NET_WORTH,
    HOMEOWNER
FROM SIGNAL_PRODUCTS.LUXURY_AUTOMOBILE_AUDIENCE
WHERE INTENT_DATE = (SELECT MAX(INTENT_DATE) FROM SIGNAL_PRODUCTS.LUXURY_AUTOMOBILE_AUDIENCE)
  AND BMW_INTENT = TRUE
  AND MERCEDES_BENZ_INTENT = TRUE
  AND SIGNAL_STRENGTH >= 0.6
ORDER BY SIGNAL_STRENGTH DESC;

SELECT
    HEM,
    MAIDS,
    SIGNAL_STRENGTH,
    SUSTAINED_INTENT_DAYS,
    STATE,
    CITY,
    AGE_RANGE,
    INCOME_RANGE,
    NET_WORTH,
    ROLLS_ROYCE_INTENT::INT
        + BENTLEY_INTENT::INT
        + FERRARI_INTENT::INT
        + LAMBORGHINI_INTENT::INT
        + BUGATTI_INTENT::INT
        + MCLAREN_INTENT::INT
        + ASTON_MARTIN_INTENT::INT
        + MASERATI_INTENT::INT                                          AS ultra_luxury_brand_count
FROM SIGNAL_PRODUCTS.LUXURY_AUTOMOBILE_AUDIENCE
WHERE INTENT_DATE = (SELECT MAX(INTENT_DATE) FROM SIGNAL_PRODUCTS.LUXURY_AUTOMOBILE_AUDIENCE)
  AND (ROLLS_ROYCE_INTENT = TRUE
       OR BENTLEY_INTENT = TRUE
       OR FERRARI_INTENT = TRUE
       OR LAMBORGHINI_INTENT = TRUE
       OR BUGATTI_INTENT = TRUE
       OR MCLAREN_INTENT = TRUE
       OR ASTON_MARTIN_INTENT = TRUE
       OR MASERATI_INTENT = TRUE)
  AND HOMEOWNER = 'true'
  AND NET_WORTH IN ('$500,000 to $749,999', '$750,000 to $999,999', 'More than $1,000,000')
ORDER BY ultra_luxury_brand_count DESC, SIGNAL_STRENGTH DESC
LIMIT 1000;

WITH brand_flags AS (
    SELECT *
    FROM SIGNAL_PRODUCTS.LUXURY_AUTOMOBILE_AUDIENCE
    WHERE INTENT_DATE = (SELECT MAX(INTENT_DATE) FROM SIGNAL_PRODUCTS.LUXURY_AUTOMOBILE_AUDIENCE)
      AND BRAND_INTEREST_DAILY = 'Multi-Brand'
)
SELECT 'Mercedes-Benz & BMW'   AS brand_pair, SUM(CASE WHEN MERCEDES_BENZ_INTENT = TRUE AND BMW_INTENT = TRUE THEN 1 ELSE 0 END) AS overlap FROM brand_flags
UNION ALL SELECT 'Mercedes-Benz & Tesla',  SUM(CASE WHEN MERCEDES_BENZ_INTENT = TRUE AND TESLA_INTENT = TRUE THEN 1 ELSE 0 END) FROM brand_flags
UNION ALL SELECT 'BMW & Tesla',            SUM(CASE WHEN BMW_INTENT = TRUE AND TESLA_INTENT = TRUE THEN 1 ELSE 0 END) FROM brand_flags
UNION ALL SELECT 'BMW & Audi',             SUM(CASE WHEN BMW_INTENT = TRUE AND AUDI_INTENT = TRUE THEN 1 ELSE 0 END) FROM brand_flags
UNION ALL SELECT 'Mercedes-Benz & Audi',   SUM(CASE WHEN MERCEDES_BENZ_INTENT = TRUE AND AUDI_INTENT = TRUE THEN 1 ELSE 0 END) FROM brand_flags
UNION ALL SELECT 'Porsche & Ferrari',      SUM(CASE WHEN PORSCHE_INTENT = TRUE AND FERRARI_INTENT = TRUE THEN 1 ELSE 0 END) FROM brand_flags
UNION ALL SELECT 'Tesla & Lincoln',        SUM(CASE WHEN TESLA_INTENT = TRUE AND LINCOLN_INTENT = TRUE THEN 1 ELSE 0 END) FROM brand_flags
UNION ALL SELECT 'Land Rover & Volvo',     SUM(CASE WHEN LAND_ROVER_INTENT = TRUE AND VOLVO_INTENT = TRUE THEN 1 ELSE 0 END) FROM brand_flags
UNION ALL SELECT 'Lexus & Cadillac',       SUM(CASE WHEN LEXUS_INTENT = TRUE AND CADILLAC_INTENT = TRUE THEN 1 ELSE 0 END) FROM brand_flags
UNION ALL SELECT 'Ferrari & Lamborghini',  SUM(CASE WHEN FERRARI_INTENT = TRUE AND LAMBORGHINI_INTENT = TRUE THEN 1 ELSE 0 END) FROM brand_flags
UNION ALL SELECT 'Rolls-Royce & Bentley',  SUM(CASE WHEN ROLLS_ROYCE_INTENT = TRUE AND BENTLEY_INTENT = TRUE THEN 1 ELSE 0 END) FROM brand_flags
UNION ALL SELECT 'Aston Martin & McLaren', SUM(CASE WHEN ASTON_MARTIN_INTENT = TRUE AND MCLAREN_INTENT = TRUE THEN 1 ELSE 0 END) FROM brand_flags
ORDER BY overlap DESC;