Customer churn 🔄

Tip

Solution to the following problem:

• customer-churn.md

Result Set

Regardless of the database, the result set should look like:

user_id	days_active_last_week
1	4

Expand for the DDL

solution(user_id, days_active_last_week) as (
    values
        (1, 4)
)

Solution

Some SQL solutions per database are provided below.

DuckDB

select
    user_id,
    bit_count((activity_history >> 7) & (power(2, 7)::int - 1)) as days_active_last_week
from user_history
where 1=1
    /* Active last week... */
    and (activity_history >> 7) & (power(2, 7)::int - 1) > 0
    /* ...and inactive this week */
    and activity_history & (power(2, 7)::int - 1) = 0