Mr--John-Doe

WITH
   union_tab as (
      select username, 'daily_activity' as label, value from web.daily_activity UNION ALL
      select username, 'user_address' as label, value from web.user_attributes_1 UNION ALL
      select username, 'user_address' as label, value from web.user_attributes_1 UNION ALL
      select username, 'user_mobile' as label, value from web.user_attributes_2 UNION ALL
      select username, 'user_email_optin' as label, value from web.user_attributes_3 UNION ALL
      select username, 'user_mobile_optin' as label, value from web.user_attributes_4 UNION ALL
      (... and so on ...)
   )

Mr--John-Doe

SELECT da.username
     , da.value:date               as date
     , da.value:hit_count          as hit_count
     
     , ua_1.value:user_address     as user_address
     
     , ua_2.value:user_mobile      as user_mobile
     
     , ua_3_optin.user_email_optin as user_email_optim
     , ua_3_optin.optin_date       as user_email_optin_date

Mr--John-Doe

WITH FULL_SET AS (
   SELECT username, hit_count, NULL AS user_address, NULL AS user_mobile, NULL AS user_email_optin
   FROM web.user_activity_summary
   UNION ALL
   SELECT username, NULL, user_address, NULL, NULL
   FROM web.user_attributes_1
   WHERE ua_1.attribute_name = 'MAIN_ADDRESS'
   UNION ALL
   SELECT username, NULL, NULL, user_mobile, NULL
   FROM web.user_attributes_2

Mr--John-Doe

SELECT da.username
     , da.date
     , da.hit_count
     , ua_1.value as user_address
     , ua_2.value as user_mobile
     , ua_3.value as user_email_optin
FROM web.daily_activity da
   LEFT JOIN web.user_attributes_1 ua_1
             ON ua_1.USERNAME = da.USERNAME
             AND ua_1.attribute_name = 'MAIN_ADDRESS'

Mr--John-Doe

WITH first_user_login AS (
   SELECT user, MIN(login_ts) AS min_date
   FROM logins
   GROUP BY 1
)
SELECT user, platform as first_login_platform, ...
FROM logins
WHERE (user, login_ts) IN (SELECT user, min_date FROM first_user_login)

Mr--John-Doe

SELECT username
     , account_id
     , session_id
     , ip_address
     , country_id
     , website_entry_point
     , payment_platform
     , platform_type
     , session_duration_s
     , number_sold_items

Mr--John-Doe

SELECT DISTINCT username
              , account_id
              , FIRST_VALUE(session_id) OVER (PARTITION BY account_id ORDER BY login_ts)          AS session_id
              , FIRST_VALUE(ip_address) OVER (PARTITION BY account_id ORDER BY login_ts)          AS ip_address
              , FIRST_VALUE(country_id) OVER (PARTITION BY account_id ORDER BY login_ts)          AS country_id
              , FIRST_VALUE(website_entry_point) OVER (PARTITION BY account_id ORDER BY login_ts) AS website_entry_point
              , FIRST_VALUE(payment_platform) OVER (PARTITION BY account_id ORDER BY login_ts)    AS payment_platform
              , FIRST_VALUE(platform_type) OVER (PARTITION BY account_id ORDER BY login_ts)       AS platform_type
              , FIRST_VALUE(session_duration_s) OVER (PARTITION BY account_id ORDER BY login_ts)  AS session_duration_s
              , FIRST_VALUE(number_sold_items) OVER (PARTITION BY account_id ORDER BY login_ts)   AS number_sold_items

Mr--John-Doe

....

SET FROM_DATE = $RUN_DATE-7;
SET TO_DATE = $RUN_DATE;

/* We don't need much from before $FROM_DATE, just the max(login_date) */
CREATE TEMP TABLE temp_previous_login__pre_window AS
  SELECT username       AS username
      , MAX(login_date) AS pre_window_login_date
  FROM sales_site.login

Mr--John-Doe

WITH base as (	
  SELECT *	
      , LAG(login_date) OVER (PARTITION BY username ORDER BY date) AS previous_login_date	
  FROM sales_site.login	
)	
SELECT *
FROM base
WHERE login_date between $FROM_DATE and $TO_DATE;

Mr--John-Doe

WITH relevant_set AS (
   SELECT *
        -- has_started_raining means: (previous!=Wet AND current=Wet)
        , LAG(road_cond) OVER (ORDER BY accident_ts, police_ref)        AS prev_road_cond
        , prev_road_cond != '2. Wet/Damp' AND road_cond = '2. Wet/Damp' AS has_started_raining
        , CASE WHEN has_started_raining THEN accident_ts END            AS time_started_raining
   FROM temp.CAMBRIDGESHIRE_ACCIDENTS
)

SELECT accident_ts