Masterclass: Game Analysis With SQL

Game Analytics Database Setup: How to Install a local MySQL database server, setup tables and load course data. How to install an SQL client tool (DBEAVER) for querying and accessing the data. How to validate your database installation.      

Jupyter Notebooks: How to install and use Jupyter, a web-based notebook for analytics. How to use Jupyter to analyze data using SQL and Python. How to use Python libraries for game analysis. Common Python libraries for statistics, visualization, machine learning and more. 

Data Collection, Storage & Access: How is game event data captured and stored. Which cloud and storage technology is best suited to which type of game analysis.  Example data capture service and data load utility in action for a popular word game. 

SELECTing Data:  How to create tables with the CREATE TABLEs statement. How the SELECT statement works. How to use various result set filtering and limiting functions. How to use the database dictionary to understand database and table structures. 

Functions & Operators:  SQL math and string functions. Comparative and logical operators. The fundamental functions and operators used for complex data analysis in gaming. 

Dates & Times: Data types and their differences. Conversion from one data type to another? How to manipulate time and dates with INTERVALs. Time difference functions, date addition and date subtraction. Common date and time conversion examples used in game analysis. 

The CASE Statement: How to use the CASE statement to transform and re-classify game data. How to PIVOT and rotate data for analysis and reporting. Several examples of CASE statement usage, common in mobile game analysis. 

Joining & Aggregating Data: Why is joining and aggregating data important. The GROUP BY statement. How to combine data from multiple tables efficiently. Use of different SQL join types.

Common Game Metrics:  TOP 10 gaming metrics by category. The SQL coding required to generate each metric from raw data.   

Window Functions: How window functions can simplify complex game data analysis. Window function FRAMEs, PARTITIONs and ORDERing. RANKing and percentile functions. Rolling averages, cumulative sums and improving game analysis efficiency and insight.

Advanced Game Analysis: Using SQL to analyze game economies. Understanding game network health. Using SQL to monitor game inflows and outflows. Advanced monetization analysis and pricing. How to determine economy balance. How to use SQL for cohort analysis and LTV prediction. 

Predictive Models & Dashboards: SQL techniques for preparing data for dashboards, reports and predictive models. Examples of churn and payer prediction using SQL. Combining SQL extraction, data and Python libraries to forecast player behavior. 

Common Table Expressions (CTEs): How to use CTEs. The purpose of CTEs and code maintainability. Reducing queries complexity and improving analysis throughout. Recursive queries and when to use CTEs, sub-queries and temporary tables.

Query Optimization: The best way to run queries against large datasets. The impact of indexes. Which index type is best suited to which data. The EXPLAIN function and  query performance. How to sample with SQL and increase query throughput. 

Player Segmentation: How we can use SQL to discover player segments. Advanced analysis techniques using SQL and Python for K-Means clustering and decision trees.  Using SQL and Python for visualization and model development. 

Capstone Project: Capstone project requirements. Capstone project template and recommended approach. How to utilize assignments and course work for your capstone project.