App Store Cohort Analysis — SQL and pandas

Weekly registration cohorts and funnel conversion on app-store event data: window functions, DATE_TRUNC cohorting, user-level conversion rates, and the same analysis implemented twice (SQL and pandas) with an assertion that the two agree.

Originally written as the VK RuStore data-analyst internship task (description, Russian); reworked here into a runnable, self-verifying case.

---

Running it

Everything runs on DuckDB — no database server, no credentials.

git clone https://github.com/semthedev/vk-rustore-data-analytics-test-task.git
cd vk-rustore-data-analytics-test-task

python -m venv .venv && source .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install -r requirements.txt

python run_queries.py                       # runs both SQL answers, prints results
jupyter lab notebooks/02_cohort_conversion_pandas.ipynb

All three answers read the same fixture, data/seed_events.sql, so the results are directly comparable.

---

The data

One event table, as specified in the task:

column	meaning
userId	user identifier
eventName	launch, register, pageVisit, download, buy, update
time	event timestamp
product	app the event refers to; NULL for store-level events

---

Task 1 — weekly cohorts, conversion to first download

sql/01_weekly_cohort_conversion.sql

Bucket users by the week of their first register event, then measure the share who made their first download in that same week.

week	users	CR
2023-02-27	3	66.67%
2023-03-06	6	83.33%
2023-03-13	2	50.00%
2023-03-20	3	33.33%

Two things I would flag before this went on a dashboard:

• The first cohort is a partial week. DATE_TRUNC('week', ...) snaps to Monday, so users who registered on 2023-03-01 land in the week beginning 2023-02-27 — a 5-day cohort compared against 7-day ones. Either drop it or align the window to the data start.
• Same-week conversion penalises users who register late in the week. A Sunday registrant has hours to convert; a Monday registrant has a full week. The 83% → 33% decline may be a composition effect rather than a real drop. The metric a product team usually wants is conversion within N days of registration, which is what task 3 uses and which does not depend on where the week boundary falls.

Task 2 — the same query in pandas

notebooks/02_cohort_conversion_pandas.ipynb

A reimplementation that is only useful if it agrees with the SQL, so the notebook ends with pd.testing.assert_frame_equal against the query output rather than a visual comparison.

Two details that decide whether the numbers match:

• DATE_TRUNC('week', ...) is Monday-based; the pandas equivalent is .dt.to_period("W").dt.start_time, not .dt.week or a 7-day resample. Off by one day and users move between cohorts.
• The registration↔download join must be a left join. An inner join drops users who never downloaded — exactly the denominator of the conversion rate — and silently inflates CR.

Task 3 — top-3 products in the best-converting cohort

sql/03_top_products_in_best_cohort.sql

Find the cohort with the highest conversion into a purchase within 7 days of registration, then rank that cohort's products by distinct buyers using RANK() OVER (...).

week	product	rating
2023-02-27	p4	1
2023-02-27	p5	1
2023-02-27	p6	1

Three products tie at rank 1 — one buyer each — which RANK() reports correctly rather than inventing an order.

The interesting part is choosing the cohort. "Cohort conversion into purchase" is a user-level rate: distinct buyers ÷ cohort size, computed before splitting by product. Computing conversion per product and taking the maximum answers a different question — it finds the week containing one strong product, not the week that converted best.

And ranking cohorts by rate alone is a trap here. The intermediate table shows why:

reg_week	users	buyers	cr
2023-02-27	3	3	100.0%
2023-02-20	2	2	100.0%
2023-02-13	1	1	100.0%
2023-03-06	6	0	0.0%
2023-03-13	2	0	0.0%
2023-03-20	3	0	0.0%

Three cohorts tie at 100%, and the smallest is a single user. A naive ORDER BY cr DESC LIMIT 1 would report that one-user cohort as the best-performing week. The query tie-breaks by cohort size so the answer is the one with the most evidence behind it; in production this belongs as a minimum cohort-size threshold agreed with the product team, not as a tie-break. run_queries.py prints this diagnostic table alongside the answer, because the top line alone hides the tie.

---

Layout

.
├── data/seed_events.sql                          # shared fixture for all three answers
├── sql/
│   ├── 01_weekly_cohort_conversion.sql
│   └── 03_top_products_in_best_cohort.sql
├── notebooks/02_cohort_conversion_pandas.ipynb   # task 2 + agreement check against task 1
├── run_queries.py                                # runs the SQL on DuckDB, prints results
├── docs/task-description-ru.pdf                  # original task text (Russian)
└── requirements.txt