Google Play Store Analytics — End-to-End SQL Portfolio Project

A complete, database-backed analytics project on the Google Play Store apps and their user reviews. Everything runs through PostgreSQL — from a 3NF/star-schema data model, through a carefully validated cleaning pipeline, to 30+ analytical queries, views, indexes, stored procedures, and audit triggers. A Python layer executes the pipeline, exports every result set, and renders charts; an executed Jupyter notebook is committed pre-rendered so the whole story is verifiable without a running kernel.

Why this project exists. It started with a simple question: what would it take to turn a messy, real-world dataset — 10,000+ Google Play apps and their reviews — into something you can trust and actually ask questions of? This project is my answer, built the way I like to work: layered schema design, data-quality gates that fail the build, window functions, recursive CTEs, query-optimization reasoning, and database-level business logic. Every output is checked into the repo, so the results are verifiable rather than hand-waved.

What the data told us. The numbers reward a close look: 92.2% of apps are free; the median paid price is $2.99 but the average is $13.01, because a handful of expensive apps skew the mean; and star ratings only weakly track review sentiment (correlation 0.268) — a good rating is not the same as a happy reviewer. The full story is in the charts below.

---

Contents

• Repository layout
• Dataset
• The pipeline (16 SQL scripts)
• Schema design
• Data cleaning decisions
• Analytics covered
• Python layer
• Key results
• Results gallery
• How to run it
• Requirements
• How it was built (AI-assisted)

---

Repository layout

google-play-sql-analytics/
├── data/
│   ├── raw/                 # source CSVs + import-ready copies (+ quarantine/)
│   └── cleaned/             # cleaned layer exported by 03
├── sql/                     # the 16-script pipeline (numbered)
│   ├── 01_schema.sql
│   ├── 02_import.sql
│   ├── 03_data_cleaning.sql
│   ├── 04_data_validation.sql
│   ├── 05-11_analytics...
│   ├── 12_analytics_views.sql
│   ├── 13_indexes.sql
│   ├── 14_query_optimization.sql
│   ├── 15_stored_procedures.sql
│   └── 16_triggers.sql
├── src/
│   ├── database.py          # SQLAlchemy engine (env-var driven)
│   ├── import_data.py       # pre-flight validation + repair + COPY load
│   ├── execute_queries.py   # runs the pipeline, exports every result to reports/
│   └── visualization.py     # 10 charts from report CSVs
├── notebooks/
│   └── play_store_analytics.ipynb   # executed notebook (live outputs checked in)
├── reports/                 # every query result exported as CSV
├── docs/
│   ├── screenshots/         # charts rendered by visualization.py
│   ├── ERD.md               # table-by-table schema documentation
│   ├── DATA_DICTIONARY.md   # column-level reference
│   └── DECISIONS.md         # every assumption/decision, numbered
└── README.md

---

Dataset

Two public Kaggle CSV files (2018 snapshot, English subset):

File	Rows	Columns	Notes
Play Store Data.csv	10,841	13	app catalog snapshot
User Reviews.csv	64,295	5	translated reviews + TextBlob sentiment

Known raw-data issues handled by the pipeline (see data cleaning):

• 798 duplicated app names, incl. 22 case/spacing variants
• 1 malformed row (Life Made WI-Fi Touchscreen Photo Frame) with a missing Category field — repaired with the verified value LIFESTYLE
• 26,868 reviews whose body is literally the string nan
• 54 review-apps absent from the app catalog (orphan reviews)
• NaN ratings, installs expressed as ranges like "50,000+", sizes like "Varies with device", prices up to $400.00, scientific-notation sentiment values (1.38e-17), and one empty genre cell

---

The pipeline (16 SQL scripts)

#	Script	Purpose	Layer
01	01_schema.sql	Full DDL: staging → cleaned → normalized (13 tables) + constraints + comments	DDL
02	02_import.sql	Bulk-load raw CSVs into staging (server-side COPY)	Load
03	03_data_cleaning.sql	Typing, dedup, standardization, dimensions + facts, quality gates	Clean
04	04_data_validation.sql	45 checks: referential integrity, coverage, business rules, parity	Validate
05	05_market_overview.sql	Catalog size, free/paid mix, install tiers, rating scorecard	Analytics
06	06_category_analysis.sql	Category leaderboards, rating quality, install concentration	Analytics
07	07_developer_analysis.sql	Developer footprints, market concentration, quality leaders	Analytics
08	08_review_sentiment_analysis.sql	Sentiment mix, polarity↔rating correlation, length effects, themes	Analytics
09	09_pricing_strategy.sql	Paid price distribution, price↔rating correlation, revenue by category	Analytics
10	10_time_analysis.sql	Catalog freshness, monthly update activity, seasonality, ROLLUP	Analytics
11	11_advanced_insights.sql	Engagement score, percentile ranking, recursive CTE, moving average	Analytics
12	12_analytics_views.sql	Reusable presentation views + a materialized snapshot	Presentation
13	13_indexes.sql	FK/filter/sort/full-text indexes + EXPLAIN proof	Performance
14	14_query_optimization.sql	EXPLAIN ANALYZE anti-pattern rewrites (NOT IN, IN, SARGABLE)	Performance
15	15_stored_procedures.sql	Parameterized report function, column profiler, maintenance routine	Application
16	16_triggers.sql	Generic audit logger, word-count automation, provenance guards	Application

---

Schema design

Staging → Cleaned → Normalized (3NF + star). The raw files are mirrored verbatim as TEXT in staging (audit trail). Cleaning produces typed, deduplicated tables. Normalization then extracts repeated attributes into dimensions and models the business facts:

                    ┌──────────── dim_date (2010-2030)
                    │
dim_category ◄──────┤
                    ├────── fact_app ◄──── app_genre ──► dim_genre
dim_content_rating ◄┤         │
                    │         │
dim_developer ◄─────┘         ▼
                         fact_review

• Fact fact_app: one row per unique app, with installs_min/installs_max preserving range information, and FK to date/category/rating/developer.
• Fact fact_review: one row per usable review with precomputed word/char counts and sentiment scores.
• Junction app_genre: many-to-many (an app can belong to several genres).
• Surrogate identity keys everywhere — natural keys in this dataset are unreliable (spelling variants, duplicates, "Varies with device").
• CHECK constraints enforce domain rules (rating 0–5, non-negative price, sentiment ranges) so the database rejects bad data even if ETL slips.

Full table-by-table and column-by-column documentation lives in docs/ERD.md and docs/DATA_DICTIONARY.md.

---

Data cleaning decisions

Every decision is explained and recorded in 03_data_cleaning.sql:

Decision	Rationale
Dedup on lower(app_name) keeping the latest/most-installed/reviewed record	Duplicates are legitimate (resubmissions); merging would invent data, keeping one audited row is honest
installs stored as min/max bounds + original bucket	Source reports ranges; bounds preserve info and allow arithmetic
size_mb + size_is_varying flag	"Varies with device" is a real state, not a missing value
NaN → NULL	Python's NaN is how the source marks "no value" — mapped to SQL NULL so missing values behave correctly
Reviews with body "nan" excluded; orphan reviews kept in review_cleaned but excluded from fact_review	Audit trail keeps every usable review; the analytic fact only joins to known apps
developer derived from app-name prefix (ASSUMPTION-01)	The source has no developer column; lineage is recorded in dim_developer.derivation_method
Content ratings stored with min_age guidance	Enables "apps targeting minors?" questions that raw labels cannot answer
Single malformed row repaired with verified LIFESTYLE; all other malformed rows quarantined	Fix what is provable, never guess at the rest

---

Analytics covered

Across 05–11 (~30 queries):

• Market overview — catalog size, free/paid split, install-tier distribution, rating scorecards
• Category — leaderboards, rating quality with HAVING, install concentration with running totals, genre richness
• Developer — footprint ranking (RANK), market concentration, portfolio-size vs rating buckets
• Sentiment — mix, polarity↔rating correlation (corr()), review-length effects, keyword themes
• Pricing — price percentiles, price buckets, price-vs-rating correlation, category revenue (lower-bound estimates)
• Time — how recently apps were updated, monthly update activity with LAG, yearly totals with GROUP BY ROLLUP, weekday patterns
• Advanced — a combined quality score, a percentile-based "good and popular" ranking, rating histogram, moving average (missing months handled), recursive CTE install ladder, a rough price-vs-downloads measure, category health matrix

See the executed notebook for charts and the reports/ folder for every raw result set.

---

Python layer

Script	What it does
src/database.py	Central SQLAlchemy engine, env-var credentials (PG_USER, PG_PASSWORD, …)
src/import_data.py	Pre-flight CSV validation, single known repair, quarantine for unknowns, COPY load, count verification
src/execute_queries.py	Runs each SQL script in order (handles $$ function bodies and psql \set variables) and exports every query result to reports/*.csv
src/visualization.py	10 clean, ready-to-share charts from the report CSVs
notebooks/play_store_analytics.ipynb	Executed walkthrough (17 cells) with live query outputs and charts, committed pre-rendered

---

Key results

Selected numbers from the executed pipeline (all reproducible via reports/):

• 9,639 unique apps from 10,841 source rows (1,202 duplicates removed)
• 37,427 usable reviews kept; 35,929 resolved to an app in the model
• 92.2% of the catalog is free; average paid price $13.01; median $2.99
• 64.2% positive, 22.3% negative, 13.5% neutral review sentiment
• Polarity↔rating correlation 0.268; long reviews (75+ words) drop polarity to 0.10
• Update activity peaks in the snapshot window's final months — a catalog that was actively maintained in 2018
• 45 validation checks: 35 must-pass all green, 10 informational (documented quirks)

---

Results gallery

Every chart below is rendered by src/visualization.py from the exported result sets in reports/. All 10 are in docs/screenshots/.

Market	Category

Rating health	Sentiment

Pricing	Time

Developer concentration	Quality score

---

How to run it

1. Create the database and role (one-time)

-- as the postgres superuser
CREATE DATABASE google_play_analytics;
CREATE ROLE analytics_app LOGIN PASSWORD 'analytics_pwd';
GRANT CONNECT ON DATABASE google_play_analytics TO analytics_app;
GRANT pg_read_server_files, pg_write_server_files TO analytics_app;  -- for COPY

2. Build the pipeline (psql)

psql -U analytics_app -d google_play_analytics -v ON_ERROR_STOP=1 -f sql/01_schema.sql
psql -U analytics_app -d google_play_analytics -v ON_ERROR_STOP=1 -f sql/02_import.sql
psql -U analytics_app -d google_play_analytics -v ON_ERROR_STOP=1 -f sql/03_data_cleaning.sql
psql -U analytics_app -d google_play_analytics -v ON_ERROR_STOP=1 -f sql/04_data_validation.sql

3. Run analytics + generate artifacts (Python)

pip install -r requirements.txt
python src/import_data.py --prep-only          # optional: re-validate the CSVs
python src/execute_queries.py                  # runs the pipeline, exports reports/*.csv
python src/visualization.py                    # renders docs/screenshots/*.png

The executed notebook (notebooks/play_store_analytics.ipynb) is committed pre-rendered, so it displays without a kernel. To re-execute it locally, open it in Jupyter and Run All, or run the cells via nbclient/nbconvert.

---

Requirements

• PostgreSQL 13+ (uses IDENTITY columns)
• Python 3.10+ with pandas, sqlalchemy>=2.0, psycopg2-binary, matplotlib, numpy
• The raw CSV files placed in data/raw/ as play_store_apps.csv and user_reviews.csv

---

How it was built (AI-assisted)

Working productively with AI coding assistants is a standard skill on modern engineering teams, and this project demonstrates it deliberately. Opencode, Claude Code, and Codex were used throughout — as fast drafters and debuggers, never as the source of truth.

The workflow that makes that safe:

1. Break the work into small, reviewable pieces. The pipeline is 16 small scripts, each with one job. Small units are easy for an assistant to help with — and easy for a human to review afterward.
2. Treat AI output as a colleague's draft, not a deliverable. Every generated script was read line by line and rewritten anywhere it didn't hold up to review.
3. Verify against the live database, not against vibes. Every drafted query was actually run in PostgreSQL; any number that looked off was chased down by hand.
4. Automate the trust. The fail-closed quality gates and the 45-check validation suite exist so correctness doesn't depend on how a line was drafted — it is enforced by the pipeline.
5. Ship the evidence. All 111 result sets, 10 charts, and the executed notebook are checked in, so anyone can re-run the project and confirm the results.

The outcome is what an unaided engineer would produce, reached faster by knowing where AI accelerates the work and where human judgment is non-negotiable — for example, data-ethics calls like repair only what is provable.

---

Author

Built by Mayank Batra.

• GitHub: batramayank106
• LinkedIn: Mayank Batra

---

License

Educational use. The datasets are from the public Kaggle dataset Google Play Store Apps.