[SQL Runday] HackerRank - Interviews

今から2週間も経っているようで、すでにハッカーランキングの中間テクニックが完成しています...!本当に嬉しいです
各公開招聘会のコンテストid、ハッカーid、name、total提出数、total accept views、total unique views(固有クリック率?)照会を記入します.

の合計を求める4つの列が0であれば、それを除外します.

特定コンテストは2つ以上の大学審査候補者に使用できるが、各大学は1つの審査コンテストしか開催していない.

Table

Challenges

Challenge idコラムはサマンダが忘れたコンテストIDの一つであるChallengeのIDである.

college idは候補者が担当する大学です.

View_Stats

総クリック率:候補者がこの挑戦を見たクリック率

総固有クリック率:重複する固有候補がこの挑戦を見たクリック率を排除する.maxはすべての候補者の好手だ.

Colleges

学院id:大学id

コンテストid:サマンダが使用する候補者を審査するための公募試合id(同大学が主催する公募試合ではなく、サマンダは2つ以上の大学の特定公募試合に対する候補審査に使用できる).

Working Process

の問題を読むときに混同されたのは、コンテストのテーブルの上のハッカーがコンテストを作ったハッカーだということだ.彼らは審査を受ける候補者ではない.

表とコラムの名前は性格に合わず、意味がはっきりしないところもあるので、表の関係を正しく理解することが大切らしい.実は私の考えは少し难しいですが、解釈を见ると简単に考えられると思います...?

ですのでまとめて、Input例を基準に
大学11219候補者審査では66406競技を使用した.(ConTest T)大学11219で行われた試合には18765と47127の挑戦があった.(Challengs T)したがって、各チャレンジのクエリ数とコミット回数を表示する場合は、
47127チャレンジ)クリック率26+15
18765チャレンジ)クリック率43+72
=総クリック率156
このように総提出を求めて、受け入れる提出、総クリック率、固有クリック率でいいです.

このように見れば順番にレース-チャレンジ-view-substats表に入ればいいです

とview,subはchallenge idを基準にsum()でグループ化すればよい.

だと思ったのですが、なぜ答えではないのでしょうか??

Errors

クエリ値がNULLの場合に0に設定するには、IFNULL(sum(), 0)<を使用します.

Havingsum(どうしよう)=0<<この文法はずっと役に立たない.常に别名で生きていこうIFNULL(sum(), 0) as sumtv<<このように指定され、Having sumtv = 0を条件として所望の値が得られる.

最初の試み

select ct.contest_id, ct.hacker_id, ct.name,
ifnull(sum(v.total_views), 0) as sum_tv,
ifnull(sum(v.total_unique_views),0) as sum_tuv,
ifnull(sum(s.total_submissions), 0) as sum_ts,
ifnull(sum(s.total_accepted_submissions), 0) as sum_tas
from Contests ct
left join Colleges c on ct.contest_id = c.contest_id
left join Challenges cl on cl.college_id = c.college_id
left join View_Stats v on v.challenge_id = cl.challenge_id
left join Submission_Stats s on s.challenge_id = cl.challenge_id
group by ct.contest_id, ct.hacker_id, ct.name
having sum_tv + sum_tuv + sum_ts + sum_tas > 0
order by ct.contest_id

問題ではコンテストを基準にクエリーを要求されているので、
content>レビューのcollege>で使用される課題>各ビュー統計,substatsを用いると,このように左joinと考えやすい.
そしてgroup by contest_idはchallengeの統計データを加算します.

なぜエラーが発生したのかを見つけるために、Rose一人のidだけを調べた.

エラー

正解

過ちからもっと多くのものを得る.selectとgroupbyにchallenge_idを加え、どのように追加されたかを確認しようとした.

-- 다음 쿼리로 실행함
select ct.contest_id, ct.hacker_id, ct.name, cl.challenges_id
ifnull(sum(v.total_views), 0) as sum_tv
from Contests ct
left join Colleges c on ct.contest_id = c.contest_id
left join Challenges cl on cl.college_id = c.college_id
left join View_Stats v on v.challenge_id = cl.challenge_id
where ct.name = 'Rose'
group by ct.contest_id, ct.hacker_id, ct.name, cl.challenges_id
order by ct.contest_id

845 579 Rose 97 0 
845 579 Rose 145 170 
845 579 Rose 276 72 
845 579 Rose 345 0 
845 579 Rose 492 124 
845 579 Rose 558 0 
845 579 Rose 773 301 
845 579 Rose 791 22 
845 579 Rose 829 186 
845 579 Rose 868 0 
845 579 Rose 1003 24 
845 579 Rose 1183 107 
845 579 Rose 1322 129 
845 579 Rose 1387 91 
845 579 Rose 1483 0 
845 579 Rose 1526 20 
845 579 Rose 1681 35 
845 579 Rose 1712 156 
845 579 Rose 1827 63 
845 579 Rose 1979 135 

total_views合わせて1635ですよね?答えが出た何だっけ...
でも私は「submission stats」表と一緒に参加しました845 579 Rose 2747 3301 の誤答が出てきました~!これが問題です.なぜ1635から2747になって膨化餅になったのですか?

845 579 Rose 97 0 117 
845 579 Rose 145 170 152 
845 579 Rose 276 144 114 
845 579 Rose 345 0 139 
845 579 Rose 492 124 0 
845 579 Rose 558 0 270 
845 579 Rose 773 602 360 
845 579 Rose 791 66 207 
845 579 Rose 829 372 484 
845 579 Rose 868 0 81 
845 579 Rose 1003 48 193 
845 579 Rose 1183 107 156 
845 579 Rose 1322 516 494 
845 579 Rose 1387 91 27 
845 579 Rose 1483 0 128 
845 579 Rose 1526 20 0 
845 579 Rose 1681 70 95 
845 579 Rose 1712 156 0 
845 579 Rose 1827 126 216 
845 579 Rose 1979 135 68 

276号挑戦は2倍、773倍、791 3倍、829倍、1003倍などのポップコーンの指標を確認できる.どうしてこんなことになったの?それは.計算されたテーブルに再結合してsum計算を行うと、v stat当たりの個数は2つまたは3つになります.276回のチャレンジでは,v stat値は2個,sub stat値は2個であり,2倍である可能性がある.

845 579 Rose 276 33 31 
845 579 Rose 276 39 31 
845 579 Rose 276 33 26 
845 579 Rose 276 39 26 

検索はleft joinのみを用いてselect ct.contest_id, ct.hacker_id, ct.name, cl.challenge_id, v.total_views, s.total_submissionsを行い、結果は276個のチャレンジが33回、39回total view値を呼び出されたことを示した.これはview値33,39がsub値31,26と並んでいるためである.したがって,このような繰返し結果を除去するためにchallenge id sumテーブルをview,sub別に集計すべきである.

最終クエリー

select ct.contest_id, ct.hacker_id, ct.name,
ifnull(sum(ss.sum_ts), 0) as sumsum_ts,
ifnull(sum(ss.sum_tas), 0) as sumsum_tas,
ifnull(sum(vs.sum_tv), 0) as sumsum_tv,
ifnull(sum(vs.sum_tuv),0) as sumsum_tuv
from Contests ct
left join Colleges c on ct.contest_id = c.contest_id
left join Challenges cl on cl.college_id = c.college_id
left join (select challenge_id, sum(total_submissions) as sum_ts,
           sum(total_accepted_submissions) as sum_tas
            from Submission_Stats
            group by challenge_id)ss
            on cl.challenge_id = ss.challenge_id
left join (select challenge_id, sum(total_views) as sum_tv,
           sum(total_unique_views) as sum_tuv
            from View_Stats
            group by challenge_id)vs
            on cl.challenge_id = vs.challenge_id
group by ct.contest_id, ct.hacker_id, ct.name
having sumsum_tv + sumsum_tuv + sumsum_ts + sumsum_tas > 0
order by ct.contest_id

challer idを押してstatsをsumし、左joinで別のテーブルを探さなければなりません!
結論:1回以上sum()、sum()計算を行う必要がある場合は、接続を続行せずに別のテーブルを探して追加し、メインテーブルにマージします.あの方は簡単で速いです.
整理された文章を発行して、一度読んでみたが、難しい部分ではないような気がしたが、解くときになぜこんなに茫然として憂鬱なのか分からなかった.このように試行錯誤を経験して、間違ったところに執着して、どんどん調べていけば、頭の中に入ることができます.