lfy79001

-- pulling user page views from GA4 events
WITH base_table AS (
-- pulls relevant columns from relevant dates to decrease the size of data scanned
  SELECT
    event_name,
    event_date,
    event_timestamp,
    user_pseudo_id,
    user_id,
    device,

lfy79001

WITH base_table AS (
-- pulls relevant columns from relevant dates to decrease the size of data scanned
  SELECT
    event_name,
    event_date,
    event_timestamp,
    user_pseudo_id,
    user_id,
    device,
    geo,

lfy79001

WITH
  Params AS (
    SELECT 'Google Navy Speckled Tee' AS selected_product
  ),
  PurchaseEvents AS (
    SELECT
      user_pseudo_id,
      items
    FROM
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

lfy79001

-- pulling user page views from GA4 events
WITH base_table AS (
-- pulls relevant columns from relevant dates to decrease the size of data scanned
  SELECT
    event_name,
    event_date,
    event_timestamp,
    user_pseudo_id,
    user_id,
    device,

lfy79001

SELECT
  item_id,
  item_name,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items)
WHERE
  _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
  AND event_name IN ('add_to_cart')
GROUP BY

lfy79001

SELECT ANY_VALUE(board_type) as board, AVG(score) as average_score
FROM  (
  SELECT MAX(if(param.key = "value", param.value.int_value, NULL)) AS score,
    MAX(if(param.key = "board", param.value.string_value, NULL)) AS board_type
  FROM (
    SELECT user_pseudo_id, event_timestamp, param
    FROM `firebase-public-project.analytics_153293282.events_20180915`,
    UNNEST(event_params) AS param
    WHERE event_name = "level_complete_quickplay"
    AND (param.key = "value" OR param.key = "board")

lfy79001

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      COUNTIF(event_name = 'page_view') AS page_view_count,
      COUNTIF(event_name IN ('in_app_purchase', 'purchase')) AS purchase_event_count
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201231'
    GROUP BY 1
  )

lfy79001

WITH
  events AS (
    SELECT
      session.value.int_value AS session_id,
      COALESCE(spend.value.int_value, spend.value.float_value, spend.value.double_value, 0.0)
        AS spend_value,
      event.*

    -- Replace table name
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` AS event

lfy79001

// How many of my deliveries will be delayed due to snowfall?
/*
When it snows in excess of six inches per day, my company experiences delivery delays. How many of my deliveries were impacted during the third week of January for the previous year?
*/
WITH timestamps AS
(   
    SELECT
        DATE_TRUNC(year,DATEADD(year,-1,CURRENT_DATE())) AS ref_timestamp,
        LAST_DAY(DATEADD(week,2 + CAST(WEEKISO(ref_timestamp) != 1 AS INTEGER),ref_timestamp),week) AS end_week,
        DATEADD(day, day_num - 7, end_week) AS date_valid_std

lfy79001

  SELECT
      city, AVG(value) as avg_aqi, latitude, longitude
  FROM
      `bigquery-public-data.openaq.global_air_quality`
  WHERE
      pollutant = 'pm25'
      AND (city = 'Beijing' OR city = 'Shanghai')
      AND EXTRACT(YEAR FROM timestamp) BETWEEN 2017 AND 2021
  GROUP BY
      city, latitude, longitude