Dahel Consultant and Techies: Sales Analysis; A deep dive into Console Sales data, aimed at extracting valuable insights to enhance strategic decision-making. (The picture below is gotten from SVET Website).
Disclaimer: This is not a real company as we know this is a dataset compiled by Dahel Consultant Techies for Internship purposes.
This is a console game performance analysis. It is done by analyzing data from two tables namely Consolegames and Consoledates. The formaer table consist of 10 fileds while the later consist of 5 fields. I used SQL to analyse the entire dataset throughout the project.
The goal of this analysis is to:
- Calculate what % of local sales were made in North America
- Extract a view of the console game titles ordered by platform name in ascending order and year of release in descending order.
- Extract the first four letters of the publisher’s name for each game title
- Display all console platforms which were released either just before Black Friday or just before Christmas ( in any year).
- Order the platforms by their longevity in ascending order (i.e the platform which was available for the longest at the bottom).
- Demonstrate how to deal with the Game_Year column if the client wants to convert it to a different data type.
- Provide recommendations on how to deal with missing data in the file.
Data Retrieval:
- SQL queries to retrieve data from one or more database tables based on specified criteria.
- Use of the SELECT statement and its various clauses such as WHERE, ORDER BY, and LIMIT.
Data Filtering and Sorting:
- Filtering data based on specific conditions using the WHERE clause.
- Sorting data using the ORDER BY clause to organize results in ascending or descending order based on specified columns.
Data Aggregation:
- Proficiency in using SQL aggregate functions (e.g., SUM, AVG, COUNT, MAX, MIN) to perform calculations on groups of data.
- Knowledge of the GROUP BY clause to group rows with similar characteristics together for aggregate function calculations.
Data Transformation:
- Ability to manipulate and transform data using SQL functions such as DATE functions, string functions, and mathematical functions.
- Understanding of how to convert data types and format data for analysis.
Subquerying and CTEs:
- Proficiency in using subqueries (nested queries) to filter or aggregate data based on the results of another query.
- Understanding of Common Table Expressions (CTEs) to create temporary result sets for use within a larger query.
Window Functions:
- Ability to utilize window functions to perform calculations across a set of rows related to the current row.
- Skills in ranking data, calculating moving averages, and computing cumulative sums using window functions.
Data Analysis and Interpretation:
- Competency in analyzing SQL query results to derive insights and make data-driven decisions.
- Interpretation of query results in the context of business requirements and objectives.
Optimization and Performance Tuning:
- Knowledge of SQL query optimization techniques to improve query performance, such as indexing, query restructuring, and using appropriate join strategies.
Error Handling and Debugging:
-
Skill in identifying and resolving errors in SQL queries, including syntax errors, logic errors, and data-related issues.
The dataset for the work is gotten from Dahel Consultant Techies. It consists of two tables namely Consolegames and Consoledates. The formaer table consist of 10 fileds and 15,980 records while the later consist of 5 fields and 31 records. I studied the dataset well to gain proper insight into the dataset. You can find a link to download the Consolegames dataset here: As for the consoledates dataset, you can download it here
- Several structured queries were written to get the right tables and then saved as views in the Database on PostgreSQL. The table and views are:
Summary of the insights gained into the Console dataset performance:
I am open for entry-level to mid_level data analyst role.