Tuesday, April 27, 2021

SQL Interview - Match grand Total

 You work for a software company that released a 2-player game and you need to tally the scores.

Given the following table , write a SQL statement to determine the reciprocals and calculate their aggregate score. In the data below, players 3 and 4 have two valid entries, but their scores need to be aggregated together.


Source:

Expected Output:

Source:


Output:



Script:

with game as (

select 1 playera, 2 playerb, 124 score from dual

union all

select 3 playera, 4 playerb, 112 score from dual

union all

select 4 playera, 3 playerb, 32 score from dual)

SELECT 

    playera,

    playerb,

    score

FROM

    ( SELECT

            CASE

                WHEN playera >= playerb THEN

                    playerb

                ELSE

                    playera

            END playera,

            CASE

                WHEN playera >= playerb THEN

                    playera

                ELSE

                    playerb

            END playerb, SUM(score) AS score

        FROM game

        GROUP BY

                CASE WHEN playera >= playerb THEN playerb ELSE playera END,

                CASE WHEN playera >= playerb THEN playera ELSE playerb END

    );


No comments:

Post a Comment