Cache Efficiency

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L5

Problem

The infrastructure team wants each edge location compared to the global baseline. For each edge, show its request count, its local cache-hit percentage, and the overall cache-hit percentage across all edges repeated on every row for easy comparison, running from the best-caching edge down to the worst.

Some edges run hot. Others coast on the global average.

Tables

cdn_logs

Columns: log_id INTEGER, edge_loc TEXT, req_path TEXT, status INTEGER, bytes INTEGER, cache_hit INTEGER, served_at TEXT

Expected output

Worked solution and explanation

Why this problem exists in real interviews

Built around cdn_logs, this problem requires conditional branching with CASE combined with division-safe NULLIF guards. Interviewers watch whether candidates handle the relationship between edge_loc, req_path, status correctly under grouping pressure.

Break down the requirements

Step 1: Group by edge_loc

Read the prose literally: one row per edge_loc carrying (a) its request_count, (b) its LOCAL cache-hit percentage, and (c) the GLOBAL cache-hit percentage repeated on every row so each edge can be eyeballed against the baseline. cache_hit is a 0/1 flag, so SUM(cache_hit) counts hits and COUNT(*) counts requests.

Step 2: Compute the ratio with CASE and NULLIF

Build the per-edge layer with GROUP BY edge_loc: request_count = COUNT(*), and the local rate = 100.0 * SUM(cache_hit) / COUNT(*). The 100.0 forces float division (avoids SQLite integer truncation) and SUM(cache_hit) is exact because the flag is a clean 0/1 with no NULLs.

Step 3: Round and order

Identify the real trap for the global baseline: 'overall across all edges' must be the POOLED rate = (total hits over the whole table) / (total requests over the whole table), NOT the unweighted average of the eight per-edge percentages. Because the edge groups have unequal sizes (24 vs 26 rows), averaging per-edge rates is the wrong, weight-blind answer.

The solution

SELECT
    edge_loc,
    COUNT(*) AS request_count,
    ROUND(100.0 * SUM(cache_hit) / COUNT(*), 2) AS hit_pct,
    ROUND(
        100.0 * SUM(SUM(cache_hit)) OVER ()
              / SUM(COUNT(*)) OVER (),
        2
    ) AS overall_hit_pct
FROM cdn_logs
GROUP BY edge_loc
ORDER BY hit_pct DESC;

Common follow-up questions

• What happens to your results if `log_id` in `cdn_logs` contains trailing whitespace or mixed casing? (Tests awareness of text normalization issues that silently fragment GROUP BY results.)
• Your CASE expression branches on `edge_loc`. What happens if a new category value appears that none of your WHEN clauses match? (Tests whether the candidate uses a meaningful ELSE branch or lets unmatched rows silently become NULL.)
• `log_id` in `cdn_logs` has ~1500M distinct values. What index strategy keeps your query from doing a full table scan? (Tests whether the candidate can design indexes for high-cardinality columns and understands selectivity.)
• Could you express this same logic as a single query without CTEs or subqueries? What readability trade-off does that introduce? (Tests whether the candidate can flatten nested logic and understands when decomposition aids maintainability.)