standardSQLで休眠ユーザーをカウントする方法がわからない

Question

前提・実現したいこと

最後にアプリを開いてから７日間以上アクセスがないユーザーを休眠と定義し、日付毎の新規の休眠ユーザー数のクエリを書きたい。

---

date|user_count(新規休眠ユーザー数)|total_count(累計休眠ユーザー数)
という形にしたい。
データベースはbigqueryを使ってて、standardSQLで書いてます。
もとのカラムには、基本的なものはすべて入ってます。

よろしくお願いします。

追記：
データベースは以下のようになってます。
テーブル：日付毎に作成。普段はワイルドカードで前日までのテーブルをまとめてます
カラム：
event_date（日付毎のテーブルのため、各テーブルでは一日程のみ、という形になってます）
event_timestamp event_name
event_params__key
event_params__value__string_value
event_params__value__int_value
event_params__value__float_value
event_params__value__double_value
event_previous_timestamp
event_value_in_usd
event_bundle_sequence_id
event_server_timestamp_offset
user_id user_pseudo_id
user_properties__key
user_properties__value__string_value
user_properties__value__int_value
user_properties__value__float_value
user_properties__value__double_value
user_properties__value__set_timestamp_micros
user_first_touch_timestamp
user_ltv__revenue
user_ltv__currency
user_ltv
device__category
device__mobile_brand_name
device__mobile_model_name
device__mobile_marketing_name
device__mobile_os_hardware_model
device__operating_system
device__operating_system_version
device__vendor_id
device__advertising_id device__language
device__is_limited_ad_tracking
device__time_zone_offset_seconds
device__browser device__browser_version device__web_info
geo__continent
geo__country
geo__region
geo__city
geo__sub_continent
geo__metro
app_info__id
app_info__version
app_info__install_store
app_info__firebase_app_id
app_info__install_source
traffic_source__name
traffic_source__medium
traffic_source__source
stream_id
platform
event_dimensions

Comments

[m.ts10806]

実際のテーブル定義を提示されたほうが具体的なアドバイス（やコード）が回答で得られると思います。「基本的なものは全て入っています」だけでは本当に要件を満たすために必要な情報が全て揃っているかわかりません。

[akm2929]

すいません、追記させていただきました。アドバイスありがとうございます。

[sazi]

テーブル定義の名称だけではどの項目を使用するべきかが確定できないので、期待する回答は付かないですよ。元データおよび得たい結果を具体的な値で例示して下さい。

Answer 1

細かい背景が分かりませんが、大枠のコードは、

日付表　日付:ユニークな稼働日フィールド

:各日付ごとの７日未操作ユーザのリストクエリ

SELECT 日付表.日付 AS 個々の日付, ユーザー情報表.ユーザID, ユーザー情報表.最終アクセス日 FROM ユーザー情報表.日付表 WHERE 日付表.日付  = ユーザー情報表.最終アクセス日 + 7

上記クエリを使った日付ごとのグループ集計クエリ
このクエリは、FROM句内を上記の:各日付ごとの７日未操作ユーザのリストクエリのコードをそのまま埋めて副問い合わせの構文で書いても動くことでしょう。

SELECT 個々の日付, COUNT(*) FROM 各日付ごとの７日未操作ユーザのリストクエリ GROUP BY 個々の日付