Efficient Distinct with GROUP BY — Solution & ExplanationProblems / Efficient Distinct with GROUP BY / EditorialApproachThe anti-pattern this problem teaches you to avoid:```sql-- BAD: Two passes over the dataSELECT d.resource, d.method, COUNT(*) ...FROM (SELECT DISTINCT resource, method FROM access_log) dJOIN access_log a ON d.resource = a.resource AND d.method = a.methodGROUP BY d.resource, d.method```The optimized approach:

Single GROUP BY — deduplicates and aggregates in one scan.

Conditional aggregation — SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts errors without a separate filter.

Why This MattersThe anti-pattern scans the table twice and adds a self-join. With 400 rows it's fast, but with 1B rows:

DISTINCT scan: O(n)

Self-join: O(n × k) where k = distinct groups

Final aggregation: O(n)

Versus a single GROUP BY: O(n) — one pass, no join.Complexity

Single-pass O(n) aggregation with hash-based grouping.

Hints

GROUP BY gives you distinct groups AND lets you aggregate — no need for a separate DISTINCT.

For error_count, use SUM(CASE WHEN status_code >= 400 THEN 1 ELSE 0 END).

In PySpark, use F.when(...).otherwise(...) inside the .agg() for conditional counting.

Solve Efficient Distinct with GROUP BY yourself →