Mobile Game Analytics

Dashboard for in-game events of the application Flood It
Dashboard for in-game events of the application Flood It
-- ( 4 ) START Fourth piece of code 
-- summary results for each type of board
SELECT board_type,
score,
SUM(Count) AS Sum_count,
AVG(Count) AS Avg_count
FROM (
-- ( 3 ) START Third piece of code
-- the sum of the results of a unique player
SELECT user_pseudo_id,
board_type,
score,
COUNT(score) AS Count
FROM(
-- ( 2 ) START Second piece of code
-- board type and unique user result
SELECT user_pseudo_id,
FORMAT_TIME('%T',
TIME(TIMESTAMP_MICROS(event_timestamp))) AS Event_time,
event_name,
MAX ( if ( param.key = "value",
param.value.int_value, NULL )) AS score,
MAX ( if ( param.key = "board",
param.value.string_value, NULL )) AS board_type
FROM (-- ( 1 ) START First piece of code / QuickPlay End EventSELECT event_name,
event_timestamp,
user_pseudo_id,
param
FROM `firebase-public-project.analytics_153293282.events_20181003`,
UNNEST(event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND (param.key = "value" OR param.key = "board")
-- ( 1 ) END First piece)GROUP BY user_pseudo_id,
Event_time,
event_name
ORDER BY user_pseudo_id,
Event_time
-- ( 2 ) END Second piece)GROUP BY user_pseudo_id,
board_type,
score
-- ( 3 ) END Third piece)GROUP BY board_type,
score
ORDER BY board_type DESC,
score
-- ( 4 ) END Fourth piece
how many steps a unique user was able to pass a board of a certain size — bigquery sql
First piece of code, QuickPlay End Event
group all the games of one player, arrange them by launch time, and changes the structure of the table — bigquery sql
Second piece of code, board type and unique user result
Aggregates the results of a player, how many times and in how many steps he was able to pass a particular board — bigquery sql
Third piece of code, the sum of the results of a unique player
Fourth piece of code, summary results for each type of board

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Andy Guliev

Andy Guliev

DEEPWAVE AI LAB | Analyst - in a fun way of finding patterns