Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Posts
    3

    Unanswered: Group by clause based on condition

    tblScore contains score for each problem

    id problemID score
    ------------------------
    1 1 10
    2 2 30

    tblSubmission contains problem submissions for each user

    id user problemID accepted
    -----------------------------------------------
    1 UserA 1 0
    2 UserA 1 0
    3 UserA 1 1
    4 UserA 2 1
    5 UserB 1 0
    6 UserB 1 1
    7 UserB 2 1


    For UserA :
    - For problemID 1
    -> submitted three times
    -> rejected for first two submission and accepted on third submission.
    - For problemID 2
    -> submitted one time
    -> accepted on first submission

    For UserB :
    - For problemID 1
    -> submitted two times
    -> rejected for first submission and accepted on second submission.
    - For problemID 2
    -> submitted one time
    -> accepted on first submission

    Now I would like to process the table and want to get the following result :

    user Score
    --------------------------
    UserA 36 (6 + 30)
    UserB 38 (8 + 30)

    Explanation :
    - For each rejected submission, a -2 point penalty.
    - UserA have submitted probelmID 1
    - > score of problemID 1 is 10.
    - > first two times rejectd
    - > third time accepted.
    -> score = 10 - 4 = 6
    - UserA have submitted problemID 2
    - > score of problemID 2 is 30
    - > first time accepted. No penalty will be counted
    - > score = 30

    so final score for UserA = 30 + 6 = 36
    Similar for UserB.

    I know all I am doing is explanations. I have tried a lot with gorup by and other stuffs. May be my lack of knowledge on SQL is the problem.

    I would really appreciate if someone help me on this.

    Thank You

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Points for correct answers:
    Code:
    SELECT tblSubmission.user
         , Sum(tblScore.score) As correct_answer_score
    FROM   tblSubmission
     INNER
      JOIN tblScore
        ON tblScore.problemID = tblSubmission.problemID
    WHERE  tblSubmission.accepted = 1
    GROUP
        BY tblSubmission.user
    ;
    Penalty points:
    Code:
    SELECT user
         , Count(*) As number_of_incorrect_answers
         , Count(*) * -2 As penalty_points
    FROM   tblSubmission
    WHERE  accepted = 0
    GROUP
        BY user
    ;
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some questions for data and requirements...

    (1) Aren't there more than one accepted submissions for a combimation of (user, problemID)?
    Are there effective mechanism to prevent such data?

    (2) If submissions of a user for a problemID were all failed (and the user gave up to submit anymore for the problemID),
    how to score the (user, problemID)?
    zero?
    -2 * (number of (rejected) submissions)?
    For example:
    Code:
    id user  problemID accepted
    -----------------------------------------------
    11 UserC 1         0
    12 UserC 1         0
    13 UserC 1         0
    14 UserC 2         0
    15 UserC 2         1
    16 UserD 1         0
    17 UserD 2         0
    18 UserD 2         0
    19 UserE 2         0
    20 UserE 2         0
    21 UserE 2         0
    (3) If (2 * number of rejected submissions) was more than the score of the problemID,
    how to score for (user, problemID)?
    zero? or minus?
    For example:
    Code:
    id user  problemID accepted
    -----------------------------------------------
    22 UserF 1         0
    23 UserF 1         0
    24 UserF 1         0
    25 UserF 1         0
    26 UserF 1         0
    27 UserF 1         0
    28 UserF 1         0
    29 UserF 1         1
    30 UserF 2         0
    Last edited by tonkuma; 06-17-14 at 13:40. Reason: Add example for (3).

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •