題目 Interviews

Samantha interviews many candidates from different colleges using coding challenges and contests. Write a query to print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views for each contest sorted by contest_id. Exclude the contest from the result if all four sums are .

Note: A specific contest can be used to screen candidates at more than one college, but each college only holds screening contest.

【題目】

• 輸入: 原始資料表共5張，關係如下:
  Contests(比賽者對應比賽ID)
  Colleges(比賽ID對應就讀的學校ID)
  Challenges(就讀的學校ID對應挑戰題目的ID)
  Submission_Stats(對應挑戰題目的ID的瀏覽次數)
  View_Stats(對應挑戰題目的ID的提交與通過次數)
• 輸出: 輸出每個比賽者參與的競賽題數與瀏覽總次數等資訊

【解題邏輯】

1. Contests、Colleges、Challenges三張table可直接join
2. Submission_Stats、View_Stats只可透過challenge_id查詢，因此需要先group by將每筆challenge_id對應到唯一的加總結果
3. 再將步驟二的結果left join回步驟一的結果
4. 題目要求若四項加總結果為0，則不需要列入計算
5. 將結果按照比賽ID進行升冪排序

    -- Oracle
    with TMP AS (
        select challenge_id, sum(total_submissions) total_submissions, sum(total_accepted_submissions) total_accepted_submissions
        from Submission_Stats
        group by challenge_id
    ), TMP2 AS (
        select challenge_id, sum(total_views) total_views, sum(total_unique_views) total_unique_views
        from View_Stats
        group by challenge_id
    )
    select O.contest_id, O.hacker_id, O.name, sum(o.ts), sum(o.tas), sum(o.tv), sum(o.tuv)
    from (
        select C.contest_id, C.hacker_id, C.name, C2.college_id, C3.challenge_id , nvl(t.total_submissions,0) ts ,nvl(t.total_accepted_submissions,0) tas, nvl(t2.total_views,0) tv , nvl(t2.total_unique_views,0)  tuv
        from Contests C, Colleges C2, Challenges C3,  TMP t, TMP2 t2
        where 1=1
        and C.contest_id = C2.contest_id
        and C2.college_id = C3.college_id
        and C3.challenge_id = t.challenge_id (+)
        and C3.challenge_id = t2.challenge_id (+)
    ) O
    group by O.contest_id, O.hacker_id, O.name
    having (sum(o.ts) + sum(o.tas) + sum(o.tv) + sum(o.tuv)) > 0
    order by 1
    ;
    
    >>> 輸出:
    845 579 Rose 1987 580 1635 566
    858 1053 Angela 703 160 1002 384
    883 1055 Frank 1121 319 1217 338
    1793 2655 Patrick 1337 360 1216 412
    2374 2765 Lisa 2733 815 3368 904
    2963 2845 Kimberly 4306 1221 3603 1184
    3584 2873 Bonnie 2492 652 3019 954
    4044 3067 Michael 1323 449 1722 528
    4249 3116 Todd 1452 376 1767 463
    4269 3256 Joe 1018 372 1766 530
    4483 3386 Earl 1911 572 1644 477
    4541 3608 Robert 1886 516 1694 504
    4601 3868 Amy 1900 639 1738 548
    4710 4255 Pamela 2752 639 2378 705
    4982 5639 Maria 2705 759 2558 711
    5913 5669 Joe 2646 790 3181 835
    5994 5713 Linda 3369 967 3048 954
    6939 6550 Melissa 2842 859 3574 1004
    7266 6947 Carol 2758 665 3044 835
    7280 7030 Paula 1963 554 886 259
    7484 7033 Marilyn 3217 934 3795 1061
    7734 7386 Jennifer 3780 1015 3637 1099
    7831 7787 Harry 3190 883 2933 1012
    7862 8029 David 1738 476 1475 472
    8812 8147 Julia 1044 302 819 266
    8825 8438 Kevin 2624 772 2187 689
    9136 8727 Paul 4205 1359 3125 954
    9613 8762 James 3438 943 3620 1046
    10568 8802 Kelly 1907 620 2577 798
    11100 8809 Robin 1929 613 1883 619
    12742 9203 Ralph 1523 413 1344 383
    12861 9644 Gloria 1596 536 2089 623
    12865 10108 Victor 2076 597 1259 418
    13503 10803 David 924 251 584 167
    13537 11390 Joyce 1381 497 1784 538
    13612 12592 Donna 1981 550 1487 465
    14502 12923 Michelle 1510 463 1830 545
    14867 13017 Stephanie 2471 676 2291 574
    15164 13256 Gerald 2570 820 2085 607
    15804 13421 Walter 1454 459 1396 476
    15891 13569 Christina 2188 710 2266 786
    16063 14287 Brandon 1804 580 1621 521
    16415 14311 Elizabeth 4535 1366 3631 1071
    18477 14440 Joseph 1320 391 1419 428
    18855 16973 Lawrence 2967 1020 3371 1011
    19097 17123 Marilyn 2956 807 2554 750
    19575 17562 Lori 2590 863 2627 760

Reference

• SQL HackerRank : Interviews