PARSE_DATE('%Y%m%d', event_date) AS event_date

CAST(FORMAT_DATE('%u', SAFE.PARSE_DATE('%Y%m%d', event_date)) AS INT64) AS day_of_week_iso

FORMAT_DATE('%A', PARSE_DATE('%Y%m%d', event_date)) AS day_of_week_name

EXTRACT(ISOWEEK FROM TIMESTAMP_MICROS(event_timestamp)) AS iso_week

_TABLE_SUFFIX BETWEEN '20260101' AND '20260101'

_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), SECOND) AS event_ts_sec

(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title

(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id

(SELECT value.float_value FROM UNNEST(event_params) WHERE key = 'value') AS value_float

(SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value') AS value_double

(SELECT COALESCE(value.int_value, value.float_value, value.double_value, SAFE_CAST(value.string_value AS FLOAT64)) FROM UNNEST(event_params) WHERE key = 'value') AS numeric_value

ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC) AS row_num

CONCAT('(',STRING_AGG(DISTINCT TO_JSON_STRING(event_name)),')') AS event_names_agg

# Insert all rows from a source table into a target table
INSERT INTO `target_table_id`
SELECT
  *
FROM
  `source_table_id`;

# Update rows in a table based on a condition
UPDATE `table_id`
SET column_a = 'new_value'
WHERE column_b = 'match_value';

# Delete rows from a table based on a condition
DELETE FROM `table_id`
WHERE column_a = 'value_a';

# Create a new table from an existing table
CREATE OR REPLACE TABLE `target_table_id` AS
SELECT
  *
FROM
  `source_table_id`;

# Create a partitioned and clustered table from an existing table
CREATE OR REPLACE TABLE `target_table_id`
PARTITION BY DATE(event_date)
CLUSTER BY column_a, column_b AS
SELECT
  *
FROM
  `source_table_id`;

# Use a Common Table Expression (CTE) to create a temporary result set
WITH base_cte AS (
  SELECT
    *
  FROM
    `source_table_id`
  WHERE
    column_a = 'filter_value'
)
SELECT
  *
FROM
  base_cte;

# Count the number of events by event name
SELECT
  event_name,
  COUNT(*) AS event_count
FROM
  `project.analytics123456.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20260101' AND '20260101'
GROUP BY ALL;

# Find duplicate ecommerce transactions by transaction_id
SELECT
  event_date,
  event_name,
  ecommerce.transaction_id,
  COUNT(*) AS event_count
FROM
  `project.analytics123456.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20260101' AND '20260101'
GROUP BY ALL
HAVING
  event_count > 1;

# Analyze consent status for selected events
SELECT
  event_name,
  privacy_info.ads_storage,
  COUNT(*) AS counter,
  ROUND((COUNT(*) * 100.0) / SUM(COUNT(*)) OVER (PARTITION BY event_name), 2) AS percentage_within_event
FROM
  `project.analytics123456.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20260101' AND '20260101'
  AND event_name IN ('page_view', 'add_to_cart', 'purchase')
GROUP BY ALL
ORDER BY
  event_name, ads_storage;

# Find and display exact duplicate rows in a table
WITH keyed AS (
  SELECT
    FARM_FINGERPRINT(TO_JSON_STRING((SELECT AS STRUCT t.*))) AS row_hash,
    t.*
  FROM
    `table_id` AS t
)
SELECT
  *
FROM
  keyed
QUALIFY
  COUNT(*) OVER (PARTITION BY row_hash) > 1
ORDER BY
  row_hash;

# Get all column names for a specific table
SELECT ARRAY_AGG(column_name ORDER BY ordinal_position) AS column_names
FROM `project.dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'your_table';