Query parameter values with SQL Data Explorer

Introduction

Use the SQL Data Explorer tool to analyze specific parameters’ values.

Below are multiple ways of analyzing a parameter value with practical examples and comments on the queries to help you better understand how to replicate those for your game.

Instructions

Every example below uses the same context: an RPG game where the player can select a character, start and complete dungeons, and use potions while they’re in a dungeon to recover health.
When replicating those queries, make sure to replace the highlighted query items to adapt them to your event names and parameters.

Queries

1. Average potions used over a specific time range (day by day)

with potionsStats as (
select
EVENT_DATE,
EVENT_JSON:potionsUsed::Integer as potions
FROM EVENTS where EVENT_DATE > CURRENT_DATE-7 and EVENT_DATE < current_date and EVENT_NAME=’dungeonCompleted’
)
select EVENT_DATE as “Date”, round(avg(potions)) as “Average Potions Used”
from potionsStats
group by EVENT_DATE
order by EVENT_DATE

Notes:

potionsStats is our sub-query.
potionsUsed is the parameter sent with the dungeonCompleted event to log how many potions were used in the dungeon.
potions is the name we’re giving to EVENT_JSON:potionsUsed::Integer
7 defines the time range we want to analyze. In this case, the last 7 days
On line 8 we call :
- The event date on the X axis.
- A round average of potions to get clean numbers but you can remove it if you want to look into decimals.
Average Potions Used to have a clean name for our round average operation on the Y axis.

2. Average potions used over a specific time range (total time range)

Use the below example if you want to understand the average potions used per player to see consumption at one point in time.

with potionsStats as (
select
EVENT_NAME,
EVENT_JSON:potionsUsed::Integer as potions
FROM EVENTS where EVENT_DATE > CURRENT_DATE-7 and EVENT_DATE < current_date and EVENT_NAME='dungeonCompleted'
)
select EVENT_NAME as “Dungeon Completed”, round(avg(potions)) as “Average Potions Used”
from potionStats
group by 1

3. Average potion used per dungeon name

Use the below example to better understand where players are using potions in the game to balance your dungeons.Use the below example to better understand where players are using potions in the game to balance your dungeons.

with potionsAndDungeons as (
select
EVENT_JSON:dungeonName::string as dungeonName,
EVENT_JSON:potionsUsed::integer as potions
FROM EVENTS where EVENT_DATE > CURRENT_DATE-7 and 
EVENT_NAME='dungeonCompleted'
)
select dungeonName as “Dungeon”, avg(potions) as "Average potions used"
from potionsAndDungeons
group by dungeonName
order by dungeonName

Notes:

We keep the same base as the two previous queries with potionsUsed and dungeonCompleted.
We’re using dungeonName, which is the parameter (logging the name of the completed dungeon) sent with the dungeonCompleted event. It will be used on the Y axis.

4. Percentage of character use in dungeons per character name

Use the below example to see the most popular characters in the game.

select
EVENT_JSON:characterName::STRING as character,
((count(EVENT_JSON:characterName::STRING)) / ( select count(*) from events
where EVENT_NAME = 'dungeonCompleted' and EVENT_DATE > current_date-7
)) as percentage
from events
where EVENT_NAME = 'dungeonCompleted' and EVENT_DATE > current_date-7
group by character
order by percentage desc

Notes:

We call characterName as character. This is the parameter sent with the dungeonCompleted event which logs the chosen character. It will be used on the X axis.
On line 3 and 4 we divide the total number of each character name per the total number of dungeons completed to get the percentage needed on the Y axis.

5. Pivot table to count the character used per dungeon name

Use the below example to have a clear view of the number of times a character is used per dungeon name. This will help better balance characters and dungeons.

select
EVENT_JSON:characterName::INTEGER,
EVENT_JSON:dungeonName::STRING,
count(EVENT_JSON:dungeonName::STRING) as dungeonCount
from events
where EVENT_NAME = 'dungeonCompleted'
and EVENT_DATE > current_date-7
group by 1,2

Notes:

Comments

The SQL Data Explorer doesn’t pivot table visualizations yet so we need to export the results to a csv and create the visualizations on a spreadsheet.
We call the character name,dungeon parameters, and the count of dungeon names.
After processing this query, you need to: