SQL skills make it easier to perform analytics tasks for any specialist who works with data. We at devtodev have prepared a series of articles that will introduce you to SQL. You will learn how to use it to analyze game and app data, how to calculate ARPPU and other monetization metrics, how to find out the structure of users' purchases, and much more. Let’s start with the SQL definition.
SQL (Structured Query Language) is an effective tool that allows you to access databases for storing, receiving, and processing information. There is a misconception that SQL is a programming language, but it would be more correct to call it a query language. We can get direct access to information about the user's actions in the game or app by making a request to the database.
Who works with SQL?
The skill of writing SQL queries is essential for any analyst. Even if an analytical system is already integrated into the product, there are non-trivial tasks when its functionality is not enough. Then the specialists turn to SQL queries to work with raw data directly.
There may be many situations when other team members need to quickly get some data without distracting the analyst from his or her primary tasks. This is why knowing the basics of SQL is useful for developers, product managers, game designers, and other specialists who work with data.
Data structure
When we talk about SQL, we imply using a relational database to manage data. It is a storage of two-dimensional tables with a fixed number of columns and an unlimited number of rows. Tables can be related to each other using the same identifiers. With their help, you can combine different tables and find the data you need. The format of tables and the links between them are always set in advance, at the stage of database design. Such tables, for example, can contain information about users and all payments made by them in the in-game store.
How to calculate the number and amount of payments for three months
Let's turn to the basic commands using a simple query from the SQL Report section as an example. It will help us calculate the number and amount of all payments made in three months in the game or app.
select to_char(eventtime, 'yyyy-mm') as month
, sum(p.priceusd)*0.7 as revenue
, count(eventtime) as payments
from p102968.payments p
where eventtime>=current_date - interval '3 month' and eventtime < current_date
and tester is false
and cheater is false
group by 1
order by 1 asc
SELECT is a required SQL command that accesses the database to create a selection of the columns we need in the given format.
The TO_CHAR function is needed to convert dates or numbers in one string, and AS gives the name to the column when creating a selection. Thus, we have grouped all payment dates by month. Now they will be named as "month".
SUM is only used for numeric columns. Here we summarize all the values in the p.priceusd column considering the grouping by month and then multiply by 0.7 to subtract 30% of the commission.
COUNT counts the number of rows in the table. By counting the number of rows in the eventtime column (date of payments), we find out how many payments were made in the selected time period.
FROM is a required query component that defines the name of the table we are working with. In the devtodev SQL report, the list of all tables and columns is conveniently located in the left section. Click on the name of the required column to add it to any part of the query.
WHERE adds additional conditions filter to the query. Here, using the CURRENT_DATE - INTERVAL and CURRENT_DATE commands, as well as comparison operators (> = and <), we set the interval for the report – the last 3 months.
The logical AND operator allows you to add additional conditions to the data selection, where we use IS FALSE to exclude testers and cheaters.
GROUP BY 1 and ORDER BY 1 ASC group the output data by the position of the field in the table (by the first column) and sort in ascending order.
Now we have finished creating the query, so we can see the result:
We’ve got 3 columns with payment data – month, revenue, and number of payments for each time period. Thus, with the help of one request, we can quickly build any report, visualize it on a chart and save it to the dashboard.
SQL syntax includes a large number of commands and operators. However, anyone who is willing to devote their time to data analysis can learn how to use it.
