cqusyc’s gists

cqusyc / users_level_online_time.sql

Created March 22, 2019 12:16

[查询注册用户某等级的游戏时长分布] #BigQuery #MergeGarden

	SELECT
	A.user_pseudo_id,
	sum(A.online_time) as online_time /* 只要同一个用户在线时长的总和 */
	FROM
	(
	SELECT user_pseudo_id, event_params.value.double_value AS online_time, event_timestamp
	FROM
	`analytics_195246954.events_*` AS T,
	T.event_params
	WHERE

cqusyc / stage_progress_count.sql

Created March 18, 2019 10:52

[查询活跃用户stage.progress.count] #BigQuery #MergeGarden

	SELECT stage, count(user_pseudo_id) as count FROM
	(
	SELECT C.user_pseudo_id, C.event_timestamp, D.stage, E.wave FROM
	(
	SELECT
	A.user_pseudo_id, max(A.event_timestamp) as event_timestamp
	FROM
	(
	SELECT
	user_pseudo_id,

cqusyc / first_open_users_stage_progress.sql

Created March 18, 2019 10:51

####[查询新注册用户最高stage.progress] #BigQuery #MergeGarden

	SELECT stage, count(user_pseudo_id) as count FROM
	(
	SELECT C.user_pseudo_id, C.event_timestamp, D.stage, E.wave FROM
	(
	SELECT
	A.user_pseudo_id, max(A.event_timestamp) as event_timestamp
	FROM
	(
	SELECT
	user_pseudo_id,

cqusyc / stage_progress_count.sql

Created March 18, 2019 10:50

####[查询留存用户stage.progress.count] #BigQuery #MergeGarden

	SELECT stage, count(user_pseudo_id) as count FROM
	(
	SELECT C.user_pseudo_id, C.event_timestamp, D.stage, E.wave FROM
	(
	SELECT
	A.user_pseudo_id, max(A.event_timestamp) as event_timestamp
	FROM
	(
	SELECT
	user_pseudo_id,

cqusyc / lost_user_stage_progress_count.sql

Created March 18, 2019 10:49

[查询流失用户stage.progress.count] #BigQuery #MergeGarden

	SELECT stage, count(user_pseudo_id) as count FROM
	(
	SELECT C.user_pseudo_id, C.event_timestamp, D.stage, E.wave FROM
	(
	SELECT
	A.user_pseudo_id, max(A.event_timestamp) as event_timestamp
	FROM
	(
	SELECT
	user_pseudo_id,

cqusyc / stage_wave_progress.sql

Last active May 31, 2022 00:18

####[查询留存用户stage.progress.wave.count] #BigQuery #MergeGarden

	SELECT stage, wave, count(user_pseudo_id) as count FROM
	(
	SELECT C.user_pseudo_id, C.event_timestamp, D.stage, E.wave FROM
	(
	SELECT
	A.user_pseudo_id, max(A.event_timestamp) as event_timestamp
	FROM
	(
	SELECT
	user_pseudo_id,

cqusyc / first_open_users_stage_wave_progress.sql

Last active May 31, 2022 00:18

####[查询新注册用户最高stage.wave.progress] #BigQuery #MergeGarden

	SELECT stage, wave, count(user_pseudo_id) as count FROM
	(
	SELECT C.user_pseudo_id, C.event_timestamp, D.stage, E.wave FROM
	(
	SELECT
	A.user_pseudo_id, max(A.event_timestamp) as event_timestamp
	FROM
	(
	SELECT
	user_pseudo_id,

cqusyc / all_track_scene.sql

Created March 8, 2019 05:08

####[查询引导流失人数] #BigQuery #MergeFood

	SELECT B.first_open,
	A1.entrance_animation_begin,
	A2.guide1_begin,
	A3.guide1_compound_food2,
	A4.guide1_enter_name_begin,
	A5.guide1_enter_name_end,
	A6.guide3_begin,
	A7.guide3_end FROM
	(
	SELECT count(user_pseudo_id) as first_open from (

cqusyc / track_scene.sql

Created March 8, 2019 04:40

####[查询引导流失人数] #BigQuery #MergeFood

	SELECT A1.entrance_animation_begin,
	A2.guide1_begin,
	A3.guide1_compound_food2,
	A4.guide1_enter_name_begin,
	A5.guide1_enter_name_end,
	A6.guide3_begin,
	A7.guide3_end FROM
	(
	SELECT count(user_pseudo_id) as entrance_animation_begin from (
	SELECT

cqusyc / stage_wave_progress_count.sql

Last active May 31, 2022 00:18

[查询活跃用户stage.wave.progress.count] #BigQuery #MergeGarden

	SELECT stage, wave, count(user_pseudo_id) as count FROM
	(
	SELECT C.user_pseudo_id, C.event_timestamp, D.stage, E.wave FROM
	(
	SELECT
	A.user_pseudo_id, max(A.event_timestamp) as event_timestamp
	FROM
	(
	SELECT
	user_pseudo_id,