Ссылка на репорт:
https://prod-uk-a.online.tableau.com/#/site/globalnet/workbooks/455998/views
Описание:
Репорт для отслеживания активности игроков по неделям по метрикам их депозитной активности и количеству дней, когда они играли.
Так же в этом репорте представлены тотал таблицы с разбивкой по количеству депозитов и количеству депозитных дней с возможностью сравнивать разные периоды.
Код sql:
WITH
regs_event AS (
SELECT
CAST(Id AS STRING) AS User,
BrandName,
CONCAT(BrandName, '', CAST(Id AS STRING)) AS Brand_User,
CAST(NULL AS STRING) AS Stage,
SignUp AS CreatedAt,
"reg" AS Event,
NULL AS Payment,
CAST(NULL AS STRING) AS Action,
CAST(NULL AS STRING) AS Success,
NULL AS Amount_EUR,
NULL AS user_action_success_number,
NULL AS newest_bonus
FROM g1-site.clean.users
),
paym_event AS (
SELECT
CAST(User AS STRING) AS User,
BrandName,
CONCAT(BrandName, '', CAST(User AS STRING)) AS Brand_User,
CAST(NULL AS STRING) AS Stage,
CreatedAt,
"paym" AS Event,
Payment,
Action,
Success,
Amount_EUR,
ROW_NUMBER() OVER(PARTITION BY User, BrandName, Action, Success ORDER BY CreatedAt) AS user_action_success_number,
NULL AS newest_bonus
FROM g1-site.clean.payments
),
bon_event AS (
SELECT
CAST(UserId AS STRING) AS User,
BrandName,
CONCAT(BrandName, '_', CAST(UserId AS STRING)) AS Brand_User,
Stage,
DateReceived AS CreatedAt,
"bonus" AS Event,
NULL AS Payment,
CAST(NULL AS STRING) AS Action,
CAST(NULL AS STRING) AS Success,
Amount_EUR,
NULL AS user_action_success_number,
ROW_NUMBER() OVER(PARTITION BY Id, BrandName ORDER BY UpdatedAt DESC) AS newest_bonus
FROM g1-site.clean.bonus_issue
),
Tags_vip_users AS (
SELECT DISTINCT Id AS User, BrandName, Tags as Tags_vip
FROM g1-site.clean.vip_users
WHERE DATE(UploadedAt) = (SELECT DATE(MAX(UploadedAt)) FROM g1-site.clean.vip_users)
)
SELECT *
FROM (
SELECT *
FROM regs_event
UNION ALL
SELECT *
FROM paym_event
UNION ALL
SELECT *
FROM bon_event
WHERE newest_bonus = 1
) AS total_tab
LEFT JOIN (
SELECT DISTINCT CAST(Id AS STRING) AS User, BrandName, Country, SignUp
FROM g1-site.clean.users
) USING(BrandName, User)
LEFT JOIN (
SELECT DISTINCT CONCAT(BrandName, '_', UserIdInCasino) AS Brand_User, Disabled, PartnerId, CampaignId --AS PartnerId_reg
FROM g1-site.clean.aff_users
) USING(Brand_User)
LEFT JOIN (
SELECT DISTINCT BrandName, PartnerId, CAST(Id AS STRING) AS CampaignId, traff_type
FROM g1-site.clean.partner_campaigns
) USING(BrandName, PartnerId, CampaignId)
LEFT JOIN (SELECT CAST(User AS STRING) AS User, BrandName, Tags_vip
FROM Tags_vip_users)
USING(BrandName, User)