Query parameter values with SQL Data Explorer#

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

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.

• 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.

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:

• The SQL Data Explorer doesn't have 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:
  1. Export the results as a CSV using the "Share" button.
  2. Open the CSV file in a spreadsheet.
  3. Create a pivot table out of the data.
  4. Create a chart out of the pivot table.