WITH
date_tab AS (
SELECT
MY_DATE,
--- 3. Извлекаем день недели
EXTRACT (DAYOFWEEK FROM MY_DATE) AS DAY_OF_WEEK
FROM (
SELECT
--- 2. Определяем границу начала календаря дат
DATE_ADD ('2020-01-01', INTERVAL param DAY) AS MY_DATE
FROM
--- 1. Создаем массив значений от нуля до тысячи с интервалом в единицу
UNNEST (GENERATE_ARRAY (0, 1000, 1)) AS param ))
--- Если предыдущее значение не равно нулю,и если количество записей больше количества предыдущих записей, то делим количество всех записей на количество предыдущих записей, а если количество записей не больше количества предыдущих, то делим количество предыдущих записей на количество всех записей по конкретному событию
SELECT
*,ROUND(IF(prev_cnt IS NOT NULL,IF(cnt > prev_cnt, ((cnt / prev_cnt)*100)-100, 100-((prev_cnt / cnt) *100)),0),1) AS diff_prev_day
FROM (
SELECT
ev.date,
DAY_OF_WEEK AS week_day,
event_name,
cnt,
--- Получаем предыдущее значение рассчитанного количества записей отсортированные по дате
LAG(cnt) OVER (PARTITION BY event_name ORDER BY date ASC) AS prev_cnt
FROM (
SELECT
--- Приводим дату в читаемый формат
PARSE_DATE("%Y%m%d", event_date) AS date,
event_name,
--- Подсчитываем количество записей в таблице по конкретному событию
COUNT(*) cnt
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
GROUP BY
event_date, event_name
--- Сортируем дату в читаемом формате по возрастанию
ORDER BY
PARSE_DATE("%Y%m%d", event_date) ASC) ev
--- Объединяем данные из временной таблицы
JOIN
date_tab
ON
date_tab.MY_DATE = ev.date
ORDER BY
event_name, ev.date ASC) lag_tab