HackerRank SQL Certification — Solutions Series

1SELECT 
2    s.roll_number, 
3    s.name
4FROM 
5    student_information s
6JOIN 
7    faculty_information f ON s.advisor = f.employee_id
8WHERE 
9    (f.gender = 'M' AND f.salary > 15000)
10    OR 
11    (f.gender = 'F' AND f.salary > 20000);

1SELECT 
2    s.roll_number, 
3    s.name
4FROM 
5    student_information s
6JOIN 
7    examination_marks e ON s.roll_number = e.roll_number
8WHERE 
9    (e.subject_one + e.subject_two + e.subject_three) < 100;

1SELECT 
2    u.id AS user_id,
3    u.first_name,
4    u.last_name,
5    c.id AS customer_id,
6    c.customer_name,
7    COUNT(co.id) AS number_of_contacts
8FROM 
9    contact co
10JOIN 
11    user_account u ON co.user_account_id = u.id
12JOIN 
13    customer c ON co.customer_id = c.id
14GROUP BY 
15    u.id, 
16    u.first_name, 
17    u.last_name, 
18    c.id, 
19    c.customer_name
20HAVING 
21    COUNT(co.id) > 1
22ORDER BY 
23    u.id ASC;

1SELECT 
2    ci.city_name || ' ' || p.product_name || ' ' || TO_CHAR(SUM(ii.line_total_price), 'FM999999990.00') AS product_sales
3FROM 
4    city ci
5JOIN 
6    customer cu ON ci.id = cu.city_id
7JOIN 
8    invoice i ON cu.id = i.customer_id
9JOIN 
10    invoice_item ii ON i.id = ii.invoice_id
11JOIN 
12    product p ON ii.product_id = p.id
13GROUP BY 
14    ci.city_name,
15    p.product_name
16ORDER BY 
17    SUM(ii.line_total_price) DESC,
18    ci.city_name ASC,
19    p.product_name ASC;

1SELECT 
2    c.algorithm,
3    COALESCE(SUM(CASE WHEN t.dt >= '2020-01-01' AND t.dt < '2020-04-01' THEN t.volume END), 0) AS q1,
4    COALESCE(SUM(CASE WHEN t.dt >= '2020-04-01' AND t.dt < '2020-07-01' THEN t.volume END), 0) AS q2,
5    COALESCE(SUM(CASE WHEN t.dt >= '2020-07-01' AND t.dt < '2020-10-01' THEN t.volume END), 0) AS q3,
6    COALESCE(SUM(CASE WHEN t.dt >= '2020-10-01' AND t.dt < '2021-01-01' THEN t.volume END), 0) AS q4
7FROM 
8    coins c
9LEFT JOIN 
10    transactions t ON c.code = t.coin_code
11GROUP BY 
12    c.algorithm
13ORDER BY 
14    c.algorithm ASC;

1WITH converted_transactions AS (
2    SELECT 
3        sender,
4        STR_TO_DATE(dt, '%Y-%m-%d %H:%i:%s') AS tx_time,
5        amount
6    FROM transactions
7),
8lagged_transactions AS (
9    SELECT 
10        sender,
11        tx_time,
12        amount,
13        LAG(tx_time) OVER (PARTITION BY sender ORDER BY tx_time) AS prev_tx_time
14    FROM converted_transactions
15),
16island_indicators AS (
17    SELECT 
18        sender,
19        tx_time,
20        amount,
21        CASE 
22            WHEN prev_tx_time IS NULL OR TIMESTAMPDIFF(SECOND, prev_tx_time, tx_time) > 3600 THEN 1 
23            ELSE 0 
24        END AS is_new_sequence
25    FROM lagged_transactions
26),
27island_groups AS (
28    SELECT 
29        sender,
30        tx_time,
31        amount,
32        SUM(is_new_sequence) OVER (PARTITION BY sender ORDER BY tx_time) AS sequence_id
33    FROM island_indicators
34)
35SELECT 
36    sender,
37    MIN(tx_time) AS sequence_start,
38    MAX(tx_time) AS sequence_end,
39    COUNT(*) AS transactions_count,
40    ROUND(SUM(amount), 6) AS transactions_sum
41FROM island_groups
42GROUP BY 
43    sender, 
44    sequence_id
45HAVING 
46    COUNT(*) >= 2 
47    AND SUM(amount) >= 150
48ORDER BY 
49    sender ASC, 
50    sequence_start ASC, 
51    sequence_end ASC;