Query parameter values with SQL Data Explorer

• 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 (selectEVENT_DATE,EVENT_JSON:potionsUsed::Integer as potionsFROM 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 potionsStatsgroup by EVENT_DATEorder by EVENT_DATE

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

with potionsStats as (selectEVENT_NAME,EVENT_JSON:potionsUsed::Integer as potionsFROM 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 potionStatsgroup by 1

3. Average potion used per dungeon name

with potionsAndDungeons as (selectEVENT_JSON:dungeonName::string as dungeonName,EVENT_JSON:potionsUsed::integer as potionsFROM EVENTS where EVENT_DATE > CURRENT_DATE-7 and EVENT_NAME='dungeonCompleted')select dungeonName as "Dungeon", avg(potions) as "Average potions used"from potionsAndDungeonsgroup by dungeonNameorder by dungeonName

• 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

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

• 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

selectEVENT_JSON:characterName::INTEGER,EVENT_JSON:dungeonName::STRING,count(EVENT_JSON:dungeonName::STRING) as dungeonCountfrom eventswhere EVENT_NAME = 'dungeonCompleted'and EVENT_DATE > current_date-7group by 1,2

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