How to calculate app uninstall rate in SQL

What is uninstall rate and why it matters

An uninstall is the bluntest negative signal a mobile product can produce. The user did not just go quiet — they walked into settings, found your icon, and explicitly removed it. For consumer mobile games, a D7 uninstall rate of 60-70% is roughly normal. For utility and SaaS apps, the same window usually sits between 20% and 30%. When your number drifts well above category norms, the cause is almost always one of three things: a broken onboarding, the wrong acquisition audience, or a first session that delivers no perceived value.

Picture the Monday after a paid acquisition push. The growth lead at a mid-sized DoorDash competitor has just spent $180k on a TikTok campaign. The PM walks over before standup and asks for the D1 and D7 uninstall rate, split by acquisition source, for last week's cohort. They want to know whether the new creative is bringing in users who stick, or users who churn before they ever place an order. You need the answer by lunch, and the underlying tables are app_installs, app_uninstalls, and app_sessions. This article walks through that query, then layers on the variants that come up at Meta, Snowflake, Linear, and Uber data analyst interviews.

Uninstall rate gets so much interview airtime because it sits at the intersection of paid acquisition efficiency and product-market fit. You can fake DAU with re-engagement campaigns. You can prop up sessions with push notifications. You cannot fake an uninstall — once the app is gone, the only path back is a fresh install, and that costs real money. A clean SQL recipe lets you trace bad CAC back to either the channel, the creative, or the first ten minutes inside the product.

The SQL formula

The headline formula is a ratio of two cohorts: how many users uninstalled within a window, divided by how many users installed in the same source cohort. The trick is to define both the install cohort and the uninstall window precisely, and to lock the cohort start far enough in the past that the window has fully elapsed.

Uninstall Rate D7 = users_uninstalled_within_7_days / users_installed * 100%

Here is the basic version you would write in Postgres or Snowflake. It restricts the install cohort to users who installed at least 30 days ago, so the D30 window is fully observed and not censored.

WITH installs AS (
    SELECT user_id, installed_at
    FROM app_installs
    WHERE installed_at BETWEEN CURRENT_DATE - INTERVAL '37 days'
                           AND CURRENT_DATE - INTERVAL '7 days'
),
uninstalls AS (
    SELECT user_id, MIN(uninstall_time) AS uninstalled_at
    FROM app_uninstalls
    GROUP BY user_id
)
SELECT
    COUNT(i.user_id) AS installs,
    COUNT(u.user_id) AS uninstalls_30d,
    COUNT(u.user_id)::NUMERIC * 100
        / NULLIF(COUNT(i.user_id), 0) AS uninstall_30d_pct
FROM installs i
LEFT JOIN uninstalls u
       ON u.user_id = i.user_id
      AND u.uninstalled_at <= i.installed_at + INTERVAL '30 days';

Three details matter. The LEFT JOIN keeps installs that have not produced an uninstall, which makes the denominator stable. NULLIF protects against zero-division when a filter empties the cohort. And MIN(uninstall_time) collapses duplicate uninstall rows for the same user — necessary if your event pipeline replays events on reconnects.

D1, D7, and D30 in one query

In practice, you almost never report just one window. D1 isolates onboarding failure, D7 isolates early engagement, and D30 isolates real retention failure. The standard interview move is to compute all three in a single pass using FILTER clauses, which is roughly twice as fast as three separate joins on large fact tables.

WITH cohort AS (
    SELECT user_id, installed_at
    FROM app_installs
    WHERE installed_at >= CURRENT_DATE - INTERVAL '60 days'
      AND installed_at <  CURRENT_DATE - INTERVAL '30 days'
),
uninstalls AS (
    SELECT user_id, MIN(uninstall_time) AS uninstalled_at
    FROM app_uninstalls
    GROUP BY user_id
)
SELECT
    COUNT(c.user_id) AS installs,
    COUNT(u.user_id) FILTER (
        WHERE u.uninstalled_at <= c.installed_at + INTERVAL '1 day'
    ) AS uninstall_d1,
    COUNT(u.user_id) FILTER (
        WHERE u.uninstalled_at <= c.installed_at + INTERVAL '7 days'
    ) AS uninstall_d7,
    COUNT(u.user_id) FILTER (
        WHERE u.uninstalled_at <= c.installed_at + INTERVAL '30 days'
    ) AS uninstall_d30,
    COUNT(u.user_id) FILTER (
        WHERE u.uninstalled_at <= c.installed_at + INTERVAL '1 day'
    )::NUMERIC * 100 / COUNT(c.user_id) AS d1_pct,
    COUNT(u.user_id) FILTER (
        WHERE u.uninstalled_at <= c.installed_at + INTERVAL '7 days'
    )::NUMERIC * 100 / COUNT(c.user_id) AS d7_pct,
    COUNT(u.user_id) FILTER (
        WHERE u.uninstalled_at <= c.installed_at + INTERVAL '30 days'
    )::NUMERIC * 100 / COUNT(c.user_id) AS d30_pct
FROM cohort c
LEFT JOIN uninstalls u USING (user_id);

Notice the install cohort window. We require installs to be at least 30 days old so the D30 figure is not censored. If you let recent installs in, D30 will be artificially low because those users have not had a chance to uninstall yet. This censorship bug is one of the easiest ways to look smart and be wrong on the same dashboard.

Splitting by acquisition source

Once the basic number is sound, every PM will immediately ask the next question: which channel is bringing in the worst users? The answer is a GROUP BY on acquisition source, plus an average days-to-uninstall column that exposes how fast users disappear after install.

SELECT
    i.acquisition_source,
    COUNT(*) AS installs,
    COUNT(u.user_id) AS uninstalls,
    COUNT(u.user_id)::NUMERIC * 100 / COUNT(*) AS uninstall_pct,
    AVG(EXTRACT(EPOCH FROM (u.uninstalled_at - i.installed_at)) / 86400)
        FILTER (WHERE u.uninstalled_at IS NOT NULL)
        AS avg_days_to_uninstall
FROM app_installs i
LEFT JOIN app_uninstalls u USING (user_id)
WHERE i.installed_at >= CURRENT_DATE - INTERVAL '90 days'
  AND i.installed_at <  CURRENT_DATE - INTERVAL '30 days'
GROUP BY i.acquisition_source
ORDER BY uninstall_pct DESC;

Two patterns show up reliably. Discount-driven channels — coupon affiliate networks, cashback partners, aggressive performance ad sets — produce the highest uninstall rates and the shortest days-to-uninstall. Organic and referral channels produce the lowest. If your TikTok creative is targeting "free $20 if you sign up", expect uninstall_pct above 70% within seven days. If your referral channel sits at 15% D30, that is a signal worth doubling down on.

Sessions before uninstall

The other useful slice is sessions before uninstall. The distribution of session counts at the moment of uninstall tells you whether you have a "front door" problem or a "value delivery" problem. Users who uninstall after zero or one sessions never got past your onboarding. Users who uninstall after fifteen sessions had a different experience entirely — they came back, tried hard, and still left.

SELECT
    sessions_before_uninstall AS sessions,
    COUNT(*) AS users
FROM (
    SELECT
        u.user_id,
        COUNT(s.session_id) AS sessions_before_uninstall
    FROM app_uninstalls u
    LEFT JOIN app_sessions s
           ON s.user_id      = u.user_id
          AND s.session_time < u.uninstalled_at
    WHERE u.uninstalled_at >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY u.user_id
) t
GROUP BY sessions_before_uninstall
ORDER BY sessions_before_uninstall;

In most consumer mobile apps, the modal user uninstalls after zero or one session. That is your single biggest growth lever — not because every zero-session uninstall is recoverable, but because moving it from 45% to 35% of all uninstalls is a measurable, ship-this-quarter project. Teams that know this distribution tend to spend less on acquisition and more on the first three minutes inside the app.

Common pitfalls

When teams calculate uninstall rate for the first time, the most common mistake is treating iOS and Android as if they expose the same signal. They do not. Apple does not directly report an uninstall event to the app or to most attribution SDKs. The standard workaround is to define "uninstalled" as "no sessions for N consecutive days" — typically 7 or 14 — and accept that you are measuring a proxy, not the underlying truth. Android pushes an explicit uninstall event through Google Play, so the two platforms are not directly comparable.

A second trap is double-counting reinstalls. A user who installs, uninstalls, and reinstalls creates two install rows and one uninstall row, and if your cohort is keyed by user_id alone, the second install incorrectly inherits the first uninstall. The fix is to key the cohort by (user_id, install_event_id) and attach uninstalls only when the timestamp falls inside that specific install's lifetime. Snowflake's QUALIFY ROW_NUMBER() makes this clean.

A third pitfall is conflating direct uninstall with phone-wipe uninstall. A user who factory-resets their phone produces the same SQL signal as a user who deleted your app in frustration. Both are real attrition, but they have different product implications. The mitigation is not to filter them out — you do not have the data — but to flag spikes that correlate with OS updates or device-replacement seasons.

A fourth trap is the wrong window for the wrong question. D1 is an onboarding metric: did users complete sign-up and find one valuable action in their first session? D7 is an engagement metric: did the product give them a reason to come back? D30 is a retention metric: is there a real habit forming? Reporting D7 when the stakeholder needs D30 produces decisions that point in the wrong direction.

A fifth trap is comparing your uninstall rate to "industry benchmarks" from random blog posts. Category variance is enormous: a hyper-casual game and a B2B utility live in different universes. Benchmark against your own cohorts week over week, or against per-category figures from data.ai.

Optimization tips

On Snowflake, Databricks, or BigQuery, the install and uninstall tables are usually the largest mobile tables you own. Three optimizations pay off quickly. First, partition app_installs and app_uninstalls by installed_at and uninstall_time respectively — daily partitions on Databricks, micro-partitions on Snowflake — so windowed cohort queries prune to a few days. Second, materialize a daily summary keyed by (install_date, acquisition_source, platform) with installs, uninstalls_d1, uninstalls_d7, uninstalls_d30. Most dashboards read from this summary, not the raw events.

Third, if you compute the "sessions before uninstall" distribution often, build an incremental table that updates only the previous day. The full join against app_sessions is expensive on any app with more than a million MAU. A MERGE into a uninstall_sessions_daily table runs in under a minute on Databricks at typical scale; the raw-table version can take fifteen.

Related reading

• How to calculate install attribution in SQL
• How to calculate churn in SQL
• How to measure app startup time in SQL
• SQL window functions interview questions

If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.

FAQ

What counts as a "healthy" uninstall rate?

It depends on the category. Hyper-casual mobile games routinely see D1 uninstall in the 50-60% range and D30 above 80%; that is normal for the genre. Mid-core games tend to land 30-45% D1 and 65-75% D30. SaaS and productivity apps from companies like Notion, Linear, and Figma sit between 20% and 40% D1, with D30 usually under 60%. Compare against your own week-over-week trend and against per-category benchmarks from data.ai, not against arbitrary thresholds.

Can I track iOS uninstalls accurately?

Not directly. Apple does not expose an uninstall event to apps or to most attribution SDKs, so the iOS uninstall rate you see in any dashboard is a proxy. The two standard proxies are silent push failures (a push that fails to deliver because the app is gone) and inactivity windows (no sessions for 7 or 14 consecutive days). Both undercount because they conflate uninstalled users with users who disabled notifications or went dormant. Treat the iOS number as a directional signal and report the methodology next to it.

A D1 uninstall rate of 50% just appeared — what should I check first?

Walk the funnel backwards. First, check whether the spike is concentrated in one acquisition source or one creative — that points to targeting drift or a misleading ad. Second, check crash rates on the affected cohort; a new release with a regression on a specific OS version can drive D1 uninstall up overnight. Third, look at onboarding completion. If users are dropping off before they reach the first valuable action, the issue is product, not acquisition.

What is the difference between uninstall and churn?

Uninstall is the explicit, observable event of removing the app from the device. Churn is the broader concept of a user no longer being active, regardless of whether the app is still installed. A user who hasn't opened your app in 60 days but still has it on their phone is churned but not uninstalled — they might come back. A user who uninstalled is both churned and made it physically harder to return, because they would have to re-download from the store. Most teams report both: uninstall rate as a precise event metric, and churn as a behavioral metric.

How should I handle reinstalls in the cohort?

Two reasonable conventions exist. The first defines the cohort by the user's first ever install and treats any subsequent reinstall as a continuation of the same user lifecycle — honest about who the user really is. The second treats each install as its own cohort entry with its own uninstall window — honest about what your acquisition spend produced. Pick one, document it on the dashboard, and stick with it.

How long should the cohort window be?

For D30 uninstall, your install cohort needs to be at least 30 days old so the window has fully elapsed for every user. A 30-day cohort ending 30 days ago — installs from day -60 to day -30 — gives a clean, uncensored D30 measurement. For weekly reporting, shorten the cohort to seven days and report D7 only. The single biggest reporting mistake is using a cohort that is still inside its own observation window.