-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathDataExpert-SQL33.sql
More file actions
92 lines (88 loc) · 2.88 KB
/
DataExpert-SQL33.sql
File metadata and controls
92 lines (88 loc) · 2.88 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
-- Question: Filtering Dance Contest Scores
-- https://www.dataexpert.io/question/dance-contest-score-filtering
-- Approach 01: Using CTEs to identify and exclude extreme scores
WITH EXCLUDE AS (
SELECT
arbiter_id AS id,
first_criterion AS criterion
FROM
playground.dance_scores
UNION ALL
SELECT
arbiter_id AS id,
second_criterion AS criterion
FROM
playground.dance_scores
UNION ALL
SELECT
arbiter_id AS id,
third_criterion AS criterion
FROM
playground.dance_scores
), MIN_MAX AS (
SELECT
id,
MAX(criterion) AS max_val,
MIN(criterion) AS min_val
FROM
EXCLUDE
GROUP BY
id
), FILTER AS (
SELECT
arbiter_id
FROM
playground.dance_scores s
LEFT JOIN
MIN_MAX m ON m.id = s.arbiter_id
GROUP BY
arbiter_id
HAVING
-- The filtering logic: keeps judges who have less than 3 extreme scores (i.e., max or min)
SUM(
CASE WHEN first_criterion IN (m.max_val, m.min_val) THEN 1 ELSE 0 END +
CASE WHEN second_criterion IN (m.max_val, m.min_val) THEN 1 ELSE 0 END +
CASE WHEN third_criterion IN (m.max_val, m.min_val) THEN 1 ELSE 0 END
) < 3
)
SELECT
*
FROM
playground.dance_scores
WHERE
arbiter_id IN (SELECT arbiter_id FROM FILTER)
ORDER BY
1;
-- Approach 02:
WITH JudgeExtremeCounts AS (
SELECT
s.arbiter_id
FROM
playground.dance_scores s
GROUP BY
s.arbiter_id
HAVING
-- 1. Calculate the judge's own highest score (max_val) and lowest score (min_val)
-- using GREATEST and LEAST across the three criteria.
-- 2. Sum the number of criteria that equal this max_val or min_val.
SUM(
CASE WHEN s.first_criterion IN (GREATEST(s.first_criterion, s.second_criterion, s.third_criterion), LEAST(s.first_criterion, s.second_criterion, s.third_criterion)) THEN 1 ELSE 0 END +
-- Check 2nd Criterion
CASE WHEN s.second_criterion IN (GREATEST(s.first_criterion, s.second_criterion, s.third_criterion), LEAST(s.first_criterion, s.second_criterion, s.third_criterion)) THEN 1 ELSE 0 END +
-- Check 3rd Criterion
CASE WHEN s.third_criterion IN (GREATEST(s.first_criterion, s.second_criterion, s.third_criterion), LEAST(s.first_criterion, s.second_criterion, s.third_criterion)) THEN 1 ELSE 0 END
) < 2
-- Filter Logic: We KEEP judges who have an extreme count of less than 2 (i.e., 0 or 1).
-- This correctly implements the rule to EXCLUDE judges who hit their extreme "for at least two criteria."
)
SELECT
ds.arbiter_id,
ds.first_criterion,
ds.second_criterion,
ds.third_criterion
FROM
playground.dance_scores ds
WHERE
ds.arbiter_id IN (SELECT arbiter_id FROM JudgeExtremeCounts)
ORDER BY
ds.arbiter_id;