App Trader, SQL for BI
App Trader, SQL for BI
Embedded Files
Project Purpose
The purpose of the project (assigned by Nashville Software School) was to provide us with a valuable learning experience encompassing SQL, teamwork, business intelligence, and reporting. This project served as a platform for us to sharpen our SQL skills, enabling us to manipulate and analyze data effectively. Collaborative teamwork was a core aspect, fostering communication, coordination, and synergy among team members. Additionally, the project emphasized the development of business intelligence acumen, honing our ability to extract meaningful insights and make data-driven decisions. Lastly, we gained proficiency in reporting, effectively communicating our findings in a clear and concise manner. Through this comprehensive project, we acquired essential skills and knowledge crucial for success in the realm of data analysis and business intelligence.
Project Idea
My team embarked on an exciting project, simulating real-world business intelligence analysis. The simulation included being entrusted by a budding company called App Trader to delve into the vast realm of mobile applications available on the Apple App Store and Android Play Store. Our objective was to explore these apps comprehensively, extracting valuable insights and unlocking hidden potential. Through meticulous analysis and advanced techniques, we empowered App Trader with the knowledge needed to make informed decisions and thrive in the competitive app market.
Data Sources
Data Sources
Nashville Software School supplied us with a wealth of data in the form of a comprehensive backup file. This resource comprised two distinct tables that captured the Apple App Store and the Android Play Store.
Data Deliverables and Insights
Data Deliverables and Insights
Develop some general recommendations about the price range, genre, content rating, or any other app characteristics that the company should target for maximum net profit.
All recommendations are based on the highest mean net profit from the respective KPIs. Recommendations are stated in descending order.
Apple Store price ranges: $4.00 - $9.99
Android Store price ranges: $0.00
Apple Store genres: Reference, Shopping, Productivity
Android Store genres: Books/Reference, Shopping, Photography
Apple Store content ratings: 9+, 17+, 12+
Android Store content ratings: Everyone 10+, Everyone, Mature 17+
Develop a Top 10 List of the apps that App Trader should buy based on profitability/return on investment as the sole priority.
App recommendations are listed in descending order
PewDiePie's Tuber Simulator, Egg, Inc., Domino's Pizza USA, The Guardian, Cytus, ASOS, Geometry Dash Lite, Honest Meditation, Fernanfloo, Bible
Develop a Top 4 list of the apps that App Trader should buy that are profitable but that also are thematically appropriate for next month's Pi Day themed campaign.
Geometry Dash, Jump Numbers, Hit the Button Math, Math Ninja HD
Technical Process
Technical Process
Tools
Tools
SQL
(EDA and Querying)
Excel
(Reporting)
SQL Code
SQL Code
My SQL code is creating a table called "store_join" by performing a join operation between two tables: "app_store_apps" and "play_store_apps". The resulting table contains various columns derived from the two original tables.
Here is a breakdown of the code:
The SELECT statement in the subquery creates new columns for the "store_join" table:
The first column named "store" determines the source of the app (either "both", "app_store", "play_store", or "error").
The following columns retrieve data from both app stores, including names, sizes, prices, review counts, ratings, content ratings, and genres.
Additional columns calculate the buying prices for each app store based on the original price and some conditions.
The SELECT statement after the table creation is used to test the new table by retrieving all rows from the "store_join" table.
The code continues with several queries labeled as "DELIVERABLES" that generate recommendations for the company based on different app characteristics. These queries analyze price ranges, genres, and content ratings to identify profitable app categories.
Finally, there are two queries that produce top 10 lists of apps based on profitability. The first query considers the buying prices of both app stores, while the second query only considers the maximum price of each app.
Overall, the code aims to analyze data from two app stores and make recommendations for App Trader based on profitability and other characteristics of the apps.
Page updated
Google Sites
Report abuse