HardCTEs High Frequency

Power User Identification via Cross-Module Adoption

Frequently Asked By:MicrosoftGoogleStripe

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

Write a CTE query to filter and identify power users from `user_activity_stream`. Calculate: 1. `user_id` 2. `distinct_modules`: Count of distinct `module_name` used 3. `total_actions`: Total event count Only keep users where `distinct_modules >= 3` AND `total_actions >= 10`. Order results by `total_actions` descending, then by `user_id` ascending.

Why Technical Interviewers Ask This

Product managers identify 'Power Users' as individuals who actively use at least 3 distinct product feature modules and generate at least 10 total interactions.

Common Pitfalls & Interview Traps

Forgetting required sorting and deterministic tie-breaking.

Not accounting for NULL or zero-value denominators during calculations.

Using inefficient quadratic subqueries instead of single-pass ctes constructs.

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

WITH user_metrics AS (
        SELECT 
          user_id,
          COUNT(DISTINCT module_name) AS distinct_modules,
          COUNT(*) AS total_actions
        FROM user_activity_stream
        GROUP BY user_id
      )
      SELECT 
        user_id,
        distinct_modules,
        total_actions
      FROM user_metrics
      WHERE distinct_modules >= 3 AND total_actions >= 10
      ORDER BY total_actions DESC, user_id ASC;

Approach Breakdown

This query addresses the problem requirements by filtering and structuring data in CTEs. It meets the target criteria: Power users utilizing at least 3 distinct modules and logging >= 10 actions.

Edge Cases to Verbally Clarify with Your Interviewer

The dataset contains zero matching records (must return valid empty result without crashing).

Multiple records tie on sorting criteria (must evaluate tie-breaking order deterministically).

Foreign keys or secondary attributes have NULL values.

Frequently Asked Follow-Up Questions

What makes this question a favorite among Microsoft and Google interviewers?

Interviewers use this problem to test whether candidates write defensive, scalable CTEs queries that run efficiently on multi-million row production databases without excessive memory usage.

How does the query optimizer execute this query?

Using single-pass table scans with index lookups or sort buffers. Running EXPLAIN (ANALYZE, BUFFERS) in the DB Exercises workbench visualizes the execution nodes and buffer hit cache.