Mini Programming Contest 2017/05 - CHECK EXPENDITURE

Problem

Your boss knows that you're a good programmer and that you're responsible, so she's given you a big task: to check the company's expenditure automatically. You're given the table expenditure_plan, which describes all the planned expenditures, and the table allowable_expenditure, which describes the amount that can be spent in certain time periods

The expenditure_plan table contains the following columns

• monday_date - the unique date of the Monday of the corresponding week
• expenditure_sum - the sum of the planned expenditure for the corresponding week
  Note: All dates in this table fall in the same year

The allowable_expenditure table contains the following columns

• id - the unique ID of the limitation
• left_bound - the unique left bound of the time period, represented by the number of the week (1-based)
• right_bound - the unique right bound of the time period, represented by the number of the week (1-based)
• value - the allowable sum that can be spent during the given left and right bounds, inclusive
  Note: The segments of the time periods don't intersect with one other. The weeks are numbered sequentially from the first week. Week 1 is the first full week of the year, meaning that its Monday is part of the current year

Your task is to return a table with the columns id and loss, where the value of loss is either 0 (if the expenditure limit with this id was not exceeded) or the amount of money by which the planned expenditure exceeded the allowable expenditure, sorted by id

Example

INPUT

Table expenditure_plan

monday_date	expenditure_sum
2016-02-08	10
2016-02-15	12
2016-06-13	5
2016-06-27	13

Table allowable_expenditure

id	left_bound	right_bound	value
1	5	8	30
2	23	26	10

OUTPUT

id	loss
1	0
2	8

In the table expenditure_plan, the Mondays correspond to the weeks with numbers 6, 7, 24, and 26 respectively. For the first time period, we add up 10 and 12 to get 22. Since 22 is smaller than 30, the loss is 0. For the second time period, we add up 5 and 13 to get 18. Since 18 is greater than 10, the allowable expendature for this time period, the answer is 18 - 10 = 8

Time limit

10000ms (MySQL)

Answer
 SELECT ae.id,
       CASE
         WHEN Sum(ep.expenditure_sum) > ae.value THEN (Sum(ep.expenditure_sum) - ae.value )
         ELSE 0
       end AS loss
FROM   allowable_expenditure AS ae
       LEFT JOIN (SELECT Week(monday_date, 7) AS week,
                         expenditure_sum
                  FROM   expenditure_plan) AS ep
              ON ae.left_bound <= ep.week
                 AND ep.week <= ae.right_bound
GROUP  BY ae.id
ORDER  BY ae.id; 

SQL Advanced Join - Interviews

Problem
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.

---

Input Format

The following tables hold interview data:

• Contests: The contest_id is the id of the contest, hacker_id is the id of the hacker who created the contest, and name is the name of the hacker.
• Colleges: The college_id is the id of the college, and contest_id is the id of the contest that Samantha used to screen the candidates.
• Challenges: The challenge_id is the id of the challenge that belongs to one of the contests whose contest_id Samantha forgot, and college_id is the id of the college where the challenge was given to candidates.
• View_Stats: The challenge_id is the id of the challenge, total_views is the number of times the challenge was viewed by candidates, and total_unique_views is the number of times the challenge was viewed by unique candidates.
• Submission_Stats: The challenge_id is the id of the challenge, total_submissions is the number of submissions for the challenge, and total_accepted_submission is the number of submissions that achieved full scores.

---

Sample Input

Contests Table: Colleges Table: Challenges Table: View_Stats Table: Submission_Stats Table:

Sample Output

66406 17973 Rose 111 39 156 56
66556 79153 Angela 0 0 11 10
94828 80275 Frank 150 38 41 15

Explanation

The contest is used in the college . In this college , challenges and are asked, so from the view and submission stats:

• Sum of total submissions
• Sum of total accepted submissions
• Sum of total views
• Sum of total unique views

Simillarly, we can find the sums for contests and .

My Answer
SELECT contests.contest_id,
       contests.hacker_id,
       contests.name,
       Sum(ss.submissions)          AS t_sub,
       Sum(ss.accepted_submissions) AS t_accepted_sub,
       Sum(vs.views)                AS t_views,
       Sum(vs.unique_views)         AS t_unique_views
FROM   contests
       LEFT JOIN colleges
              ON contests.contest_id = colleges.contest_id
       LEFT JOIN challenges
              ON colleges.college_id = challenges.college_id
       LEFT JOIN (SELECT challenge_id,
                         Sum(total_views)        AS views,
                         Sum(total_unique_views) AS unique_views
                  FROM   view_stats
                  GROUP  BY challenge_id) AS vs
              ON challenges.challenge_id = vs.challenge_id
       LEFT JOIN (SELECT challenge_id,
                         Sum(total_submissions)          AS submissions,
                         Sum(total_accepted_submissions) AS
                         accepted_submissions
                  FROM   submission_stats
                  GROUP  BY challenge_id) AS ss
              ON challenges.challenge_id = ss.challenge_id
GROUP  BY contests.contest_id,
          contests.hacker_id,
          contests.name
HAVING ( t_sub + t_accepted_sub + t_views + t_unique_views ) > 0
ORDER  BY contests.contest_id;