Project: User Behavioral Analysis Dashboard

Congratulations! You've completed **Phase 5**. You now possess the analytical skills of a top-tier senior data scientist.

Today, we're building a **User Behavioral Engine**.

The Requirement

The Product team needs to identify "Power Users."

1. Calculate the **7-day rolling average** of their actions.

2. Find their **Rank** within their country.

3. Compare their **Last Action Date** to the one before it to find their "Usage Gap."

The Master Solution

WITH user_logs AS (

SELECT

user_id,

country,

action_date,

COUNT(*) OVER(

PARTITION BY user_id

ORDER BY action_date

RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW

) as rolling_actions

FROM events

),

behavioral_metrics AS (

SELECT

user_id,

country,

rolling_actions,

action_date,

LAG(action_date) OVER(PARTITION BY user_id ORDER BY action_date) as prev_action

FROM user_logs

)

SELECT

*,

RANK() OVER(PARTITION BY country ORDER BY rolling_actions DESC) as power_user_rank

FROM behavioral_metrics;

Why this is impressive

In one query, we've handled time-series smoothing, hierarchical ranking, and historical comparison. This would have taken hundreds of lines of Python or Java to calculate manually!

**Next Phase (Days 76–85): Data Cleaning**. We're going to learn how to deal with the "Dirty Secret" of databases: bad data, malformed strings, and inconsistent formats.

See you in Phase 6!