w3resource

SQL Exercise: Find the referees who booked most number of players

SQL soccer Database: Joins Exercise-55 with Solution

55. From the following tables, write a SQL query to find those referees who booked the most number of players. Return referee name, number of matches.

Sample table: player_booked
 match_no | team_id | player_id | booking_time | sent_off | play_schedule | play_half
----------+---------+-----------+--------------+----------+---------------+-----------
        1 |    1216 |    160349 |           32 |          | NT            |         1
        1 |    1216 |    160355 |           45 |          | NT            |         1
        1 |    1207 |    160159 |           69 | Y        | NT            |         2
        1 |    1216 |    160360 |           78 |          | NT            |         2
        2 |    1221 |    160470 |           14 |          | NT            |         1
        2 |    1201 |    160013 |           23 |          | NT            |         1
        2 |    1201 |    160013 |           36 |          | NT            |         1
        2 |    1201 |    160014 |           63 |          | NT            |         2
        2 |    1221 |    160472 |           66 |          | NT            |         2
        2 |    1201 |    160015 |           89 |          | NT            |         2
        2 |    1201 |    160009 |           93 |          | NT            |         2
        3 |    1218 |    160401 |            2 |          | ST            |         2
        3 |    1218 |    160406 |           31 |          | NT            |         1
        3 |    1218 |    160408 |           78 |          | NT            |         2
        3 |    1218 |    160411 |           80 |          | NT            |         2
        3 |    1218 |    160407 |           83 |          | NT            |         2
        4 |    1206 |    160120 |           62 |          | NT            |         2
        4 |    1217 |    160377 |           72 |          | NT            |         2
        5 |    1222 |    160505 |           31 |          | NT            |         1
        5 |    1222 |    160490 |           48 |          | NT            |         2
        5 |    1204 |    160077 |           80 |          | NT            |         2
        5 |    1222 |    160502 |           91 |          | NT            |         2
        6 |    1213 |    160290 |           65 |          | NT            |         2
        6 |    1212 |    160258 |           69 |          | NT            |         2
        6 |    1213 |    160284 |           89 |          | NT            |         2
        7 |    1223 |    160518 |           68 |          | NT            |         2
        8 |    1205 |    160100 |           61 |          | NT            |         2
        9 |    1215 |    160336 |           43 |          | NT            |         1
        9 |    1220 |    160445 |           61 |          | NT            |         2
        9 |    1215 |    160341 |           77 |          | NT            |         2
       10 |    1211 |    160236 |           65 |          | NT            |         2
       10 |    1211 |    160248 |           75 |          | NT            |         2
       10 |    1211 |    160235 |           78 |          | NT            |         2
       10 |    1211 |    160245 |           84 |          | NT            |         2
       10 |    1203 |    160057 |           93 |          | NT            |         2
       11 |    1202 |    160027 |           33 |          | NT            |         1
       11 |    1202 |    160027 |           66 | Y        | NT            |         2
       11 |    1209 |    160204 |           80 |          | NT            |         2
       12 |    1210 |    160227 |            2 |          | ST            |         2
       12 |    1210 |    160221 |           55 |          | NT            |         2
       13 |    1218 |    160395 |           46 |          | NT            |         2
       14 |    1221 |    160480 |            2 |          | ST            |         2
       14 |    1216 |    160361 |           22 |          | NT            |         1
       14 |    1216 |    160357 |           24 |          | NT            |         1
       14 |    1216 |    160367 |           37 |          | NT            |         1
       14 |    1221 |    160477 |           50 |          | NT            |         2
       14 |    1216 |    160352 |           76 |          | NT            |         2
       15 |    1201 |    160015 |           55 |          | NT            |         2
       15 |    1201 |    160011 |           81 |          | NT            |         2
       15 |    1207 |    160152 |           88 |          | NT            |         2
       16 |    1224 |    160535 |           61 |          | NT            |         2
       17 |    1223 |    160528 |           40 |          | NT            |         1
       17 |    1212 |    160272 |           63 |          | NT            |         2
       17 |    1223 |    160523 |           67 |          | NT            |         2
       17 |    1212 |    160266 |           87 |          | NT            |         2
       17 |    1212 |    160259 |           90 |          | NT            |         2
       18 |    1208 |    160175 |            3 |          | NT            |         1
       18 |    1213 |    160294 |            3 |          | ST            |         2
       18 |    1208 |    160177 |           34 |          | NT            |         1
       18 |    1213 |    160293 |           45 |          | NT            |         1
       18 |    1213 |    160288 |           55 |          | NT            |         2
       18 |    1208 |    160165 |           67 |          | NT            |         2
       19 |    1211 |    160242 |           69 |          | NT            |         2
       19 |    1220 |    160447 |           89 |          | NT            |         2
       19 |    1211 |    160231 |           94 |          | NT            |         2
       20 |    1204 |    160080 |           14 |          | NT            |         1
       20 |    1205 |    160101 |           72 |          | NT            |         2
       20 |    1204 |    160081 |           74 |          | NT            |         2
       20 |    1204 |    160078 |           88 |          | NT            |         2
       21 |    1219 |    160424 |            2 |          | NT            |         1
       21 |    1222 |    160504 |            9 |          | NT            |         1
       21 |    1222 |    160500 |           41 |          | NT            |         1
       22 |    1215 |    160334 |           42 |          | NT            |         1
       22 |    1203 |    160056 |           49 |          | NT            |         2
       23 |    1209 |    160199 |            2 |          | ST            |         2
       23 |    1210 |    160229 |           42 |          | NT            |         1
       23 |    1210 |    160227 |           75 |          | NT            |         2
       23 |    1210 |    160216 |           77 |          | NT            |         2
       23 |    1209 |    160192 |           81 |          | NT            |         2
       23 |    1209 |    160197 |           83 |          | NT            |         2
       24 |    1202 |    160028 |            6 |          | NT            |         1
       24 |    1214 |    160321 |           31 |          | NT            |         1
       24 |    1214 |    160307 |           40 |          | NT            |         1
       24 |    1202 |    160037 |           47 |          | NT            |         2
       24 |    1202 |    160029 |           78 |          | NT            |         2
       24 |    1202 |    160042 |           86 |          | NT            |         2
       25 |    1201 |    160012 |            6 |          | NT            |         1
       25 |    1216 |    160353 |           54 |          | NT            |         2
       25 |    1201 |    160017 |           85 |          | NT            |         2
       25 |    1216 |    160356 |           85 |          | NT            |         2
       25 |    1216 |    160364 |           91 |          | NT            |         2
       25 |    1201 |    160007 |           95 |          | NT            |         2
       26 |    1207 |    160147 |           25 |          | NT            |         1
       26 |    1207 |    160145 |           83 |          | NT            |         2
       27 |    1224 |    160551 |           16 |          | NT            |         1
       27 |    1217 |    160383 |           64 |          | NT            |         2
       28 |    1218 |    160409 |           24 |          | NT            |         1
       28 |    1206 |    160119 |           52 |          | NT            |         2
       29 |    1223 |    160520 |           25 |          | NT            |         1
       29 |    1223 |    160513 |           38 |          | NT            |         1
       29 |    1213 |    160290 |           60 |          | NT            |         2
       31 |    1222 |    160491 |           35 |          | NT            |         1
       31 |    1205 |    160108 |           36 |          | NT            |         1
       31 |    1205 |    160107 |           39 | Y        | NT            |         1
       31 |    1222 |    160490 |           50 |          | NT            |         2
       31 |    1205 |    160112 |           87 |          | NT            |         2
       32 |    1204 |    160087 |           29 |          | NT            |         1
       32 |    1204 |    160079 |           70 |          | NT            |         2
       32 |    1204 |    160076 |           70 |          | NT            |         2
       32 |    1204 |    160085 |           88 |          | NT            |         2
       33 |    1210 |    160218 |           36 |          | NT            |         1
       33 |    1210 |    160230 |           51 |          | NT            |         2
       33 |    1202 |    160045 |           70 |          | NT            |         2
       33 |    1210 |    160220 |           78 |          | NT            |         2
       33 |    1210 |    160208 |           82 |          | NT            |         2
       34 |    1209 |    160190 |           13 |          | NT            |         1
       34 |    1209 |    160191 |           28 |          | NT            |         1
       34 |    1209 |    160203 |           34 |          | NT            |         1
       34 |    1209 |    160202 |           56 |          | NT            |         2
       35 |    1211 |    160233 |           39 |          | NT            |         1
       35 |    1215 |    160343 |           39 |          | NT            |         1
       35 |    1215 |    160332 |           73 |          | NT            |         2
       35 |    1211 |    160234 |           78 |          | NT            |         2
       35 |    1211 |    160253 |           87 |          | NT            |         2
       35 |    1211 |    160251 |           93 |          | NT            |         2
       36 |    1203 |    160064 |            1 |          | ST            |         1
       36 |    1203 |    160055 |           30 |          | NT            |         1
       36 |    1220 |    160451 |           72 |          | NT            |         2
       37 |    1221 |    160470 |           55 |          | NT            |         2
       37 |    1213 |    160282 |           58 |          | NT            |         2
       38 |    1212 |    160266 |           44 |          | NT            |         1
       38 |    1224 |    160538 |           58 |          | NT            |         2
       38 |    1212 |    160267 |           67 |          | NT            |         2
       38 |    1224 |    160544 |           92 |          | NT            |         2
       39 |    1214 |    160318 |           78 |          | NT            |         2
       40 |    1215 |    160328 |           25 |          | NT            |         1
       40 |    1207 |    160152 |           27 |          | NT            |         1
       40 |    1215 |    160334 |           41 |          | NT            |         1
       40 |    1207 |    160147 |           44 |          | NT            |         1
       40 |    1215 |    160329 |           66 | Y        | NT            |         2
       40 |    1215 |    160343 |           72 |          | NT            |         2
       41 |    1218 |    160407 |            2 |          | ST            |         2
       41 |    1218 |    160401 |           13 |          | NT            |         1
       41 |    1208 |    160169 |           46 |          | NT            |         2
       41 |    1208 |    160168 |           67 |          | NT            |         2
       42 |    1209 |    160192 |           34 |          | NT            |         1
       42 |    1209 |    160194 |           47 |          | NT            |         2
       42 |    1209 |    160196 |           61 |          | NT            |         2
       42 |    1203 |    160056 |           67 |          | NT            |         2
       42 |    1203 |    160065 |           89 |          | NT            |         2
       42 |    1203 |    160061 |           91 |          | NT            |         2
       42 |    1209 |    160207 |           92 |          | NT            |         2
       43 |    1219 |    160431 |            2 |          | ST            |         2
       43 |    1211 |    160238 |           24 |          | NT            |         1
       43 |    1219 |    160436 |           41 |          | NT            |         1
       43 |    1211 |    160252 |           54 |          | NT            |         2
       43 |    1211 |    160245 |           89 |          | NT            |         2
       43 |    1219 |    160427 |           89 |          | NT            |         2
       43 |    1219 |    160421 |           89 |          | NT            |         2
       44 |    1210 |    160208 |           38 |          | NT            |         1
       44 |    1206 |    160137 |           47 |          | NT            |         2
       44 |    1210 |    160222 |           65 |          | NT            |         2
       45 |    1214 |    160318 |            2 |          | ST            |         2
       45 |    1213 |    160282 |           42 |          | NT            |         1
       45 |    1213 |    160281 |           66 |          | NT            |         2
       45 |    1214 |    160310 |           70 |          | NT            |         2
       45 |    1213 |    160290 |           89 |          | NT            |         2
       46 |    1224 |    160535 |            5 |          | NT            |         1
       46 |    1224 |    160533 |           16 |          | NT            |         1
       46 |    1224 |    160536 |           24 |          | NT            |         1
       46 |    1203 |    160061 |           59 |          | NT            |         2
       46 |    1224 |    160544 |           75 |          | NT            |         2
       46 |    1203 |    160050 |           85 |          | NT            |         2
       47 |    1211 |    160247 |           56 |          | NT            |         2
       47 |    1211 |    160238 |           57 |          | NT            |         2
       47 |    1211 |    160246 |           59 |          | NT            |         2
       47 |    1208 |    160168 |           90 |          | NT            |         2
       47 |    1208 |    160180 |          112 |          | NT            |         2
       48 |    1210 |    160221 |           58 |          | NT            |         2
       48 |    1207 |    160149 |           75 |          | NT            |         2
       49 |    1224 |    160540 |            8 |          | NT            |         1
       49 |    1224 |    160533 |           62 |          | NT            |         2
       49 |    1214 |    160303 |           71 |          | NT            |         2
       49 |    1214 |    160322 |           72 |          | NT            |         2
       49 |    1224 |    160547 |           88 |          | NT            |         2
       50 |    1208 |    160177 |            1 |          | ST            |         1
       50 |    1208 |    160172 |           36 |          | NT            |         1
       50 |    1207 |    160143 |           43 |          | NT            |         1
       50 |    1208 |    160180 |           45 |          | NT            |         1
       50 |    1208 |    160173 |           50 |          | NT            |         2
       50 |    1207 |    160152 |           75 |          | NT            |         2
       51 |    1214 |    160304 |           34 |          | NT            |         1
       51 |    1214 |    160313 |           62 |          | NT            |         2
       51 |    1207 |    160149 |           80 |          | NT            |         2
       51 |    1214 |    160308 |           95 |          | ET            |         1
       51 |    1207 |    160153 |           97 |          | ET            |         1
       51 |    1214 |    160318 |           98 |          | ET            |         1
       51 |    1207 |    160145 |          107 |          | ET            |         2
       51 |    1207 |    160155 |          115 |          | ET            |         2
       51 |    1214 |    160306 |          119 |          | ET            |         2
       51 |    1214 |    160302 |          122 |          | ET            |         2
Sample table: match_mast
 match_no | play_stage | play_date  | results | decided_by | goal_score | venue_id | referee_id | audence | plr_of_match | stop1_sec | stop2_sec
----------+------------+------------+---------+------------+------------+----------+------------+---------+--------------+-----------+-----------
        1 | G          | 2016-06-11 | WIN     | N          | 2-1        |    20008 |      70007 |   75113 |       160154 |       131 |       242
        2 | G          | 2016-06-11 | WIN     | N          | 0-1        |    20002 |      70012 |   33805 |       160476 |        61 |       182
        3 | G          | 2016-06-11 | WIN     | N          | 2-1        |    20001 |      70017 |   37831 |       160540 |        64 |       268
        4 | G          | 2016-06-12 | DRAW    | N          | 1-1        |    20005 |      70011 |   62343 |       160128 |         0 |       185
        5 | G          | 2016-06-12 | WIN     | N          | 0-1        |    20007 |      70006 |   43842 |       160084 |       125 |       325
        6 | G          | 2016-06-12 | WIN     | N          | 1-0        |    20006 |      70014 |   33742 |       160291 |         2 |       246
        7 | G          | 2016-06-13 | WIN     | N          | 2-0        |    20003 |      70002 |   43035 |       160176 |        89 |       188
        8 | G          | 2016-06-13 | WIN     | N          | 1-0        |    20010 |      70009 |   29400 |       160429 |       360 |       182
        9 | G          | 2016-06-13 | DRAW    | N          | 1-1        |    20008 |      70010 |   73419 |       160335 |        67 |       194
       10 | G          | 2016-06-14 | WIN     | N          | 0-2        |    20004 |      70005 |   55408 |       160244 |        63 |       189
       11 | G          | 2016-06-14 | WIN     | N          | 0-2        |    20001 |      70018 |   34424 |       160197 |        61 |       305
       12 | G          | 2016-06-15 | DRAW    | N          | 1-1        |    20009 |      70004 |   38742 |       160320 |        15 |       284
       13 | G          | 2016-06-15 | WIN     | N          | 1-2        |    20003 |      70001 |   38989 |       160405 |        62 |       189
       14 | G          | 2016-06-15 | DRAW    | N          | 1-1        |    20007 |      70015 |   43576 |       160477 |        74 |       206
       15 | G          | 2016-06-16 | WIN     | N          | 2-0        |    20005 |      70013 |   63670 |       160154 |        71 |       374
       16 | G          | 2016-06-16 | WIN     | N          | 2-1        |    20002 |      70003 |   34033 |       160540 |        62 |       212
       17 | G          | 2016-06-16 | WIN     | N          | 0-2        |    20004 |      70016 |   51043 |       160262 |         7 |       411
       18 | G          | 2016-06-17 | DRAW    | N          | 0-0        |    20008 |      70008 |   73648 |       160165 |         6 |       208
       19 | G          | 2016-06-17 | WIN     | N          | 1-0        |    20010 |      70007 |   29600 |       160248 |         2 |       264
       20 | G          | 2016-06-17 | DRAW    | N          | 2-2        |    20009 |      70005 |   38376 |       160086 |        71 |       280
       21 | G          | 2016-06-18 | WIN     | N          | 3-0        |    20006 |      70010 |   33409 |       160429 |        84 |       120
       22 | G          | 2016-06-18 | WIN     | N          | 3-0        |    20001 |      70004 |   39493 |       160064 |        11 |       180
       23 | G          | 2016-06-18 | DRAW    | N          | 1-1        |    20005 |      70015 |   60842 |       160230 |        61 |       280
       24 | G          | 2016-06-19 | DRAW    | N          | 0-0        |    20007 |      70011 |   44291 |       160314 |         3 |       200
       25 | G          | 2016-06-20 | WIN     | N          | 0-1        |    20004 |      70016 |   49752 |       160005 |       125 |       328
       26 | G          | 2016-06-20 | DRAW    | N          | 0-0        |    20003 |      70001 |   45616 |       160463 |        60 |       122
       27 | G          | 2016-06-21 | WIN     | N          | 0-3        |    20010 |      70006 |   28840 |       160544 |        62 |       119
       28 | G          | 2016-06-21 | DRAW    | N          | 0-0        |    20009 |      70012 |   39051 |       160392 |        62 |       301
       29 | G          | 2016-06-21 | WIN     | N          | 0-1        |    20005 |      70017 |   58874 |       160520 |        29 |       244
       30 | G          | 2016-06-21 | WIN     | N          | 0-1        |    20007 |      70018 |   44125 |       160177 |        21 |       195
       31 | G          | 2016-06-22 | WIN     | N          | 0-2        |    20002 |      70013 |   32836 |       160504 |        60 |       300
       32 | G          | 2016-06-22 | WIN     | N          | 2-1        |    20001 |      70008 |   37245 |       160085 |        70 |       282
       33 | G          | 2016-06-22 | WIN     | N          | 2-1        |    20008 |      70009 |   68714 |       160220 |         7 |       244
       34 | G          | 2016-06-22 | DRAW    | N          | 3-3        |    20004 |      70002 |   55514 |       160322 |        70 |       185
       35 | G          | 2016-06-23 | WIN     | N          | 0-1        |    20003 |      70014 |   44268 |       160333 |        79 |       221
       36 | G          | 2016-06-23 | WIN     | N          | 0-1        |    20006 |      70003 |   34011 |       160062 |        63 |       195
       37 | R          | 2016-06-25 | WIN     | P          | 1-1        |    20009 |      70005 |   38842 |       160476 |       126 |       243
       38 | R          | 2016-06-25 | WIN     | N          | 1-0        |    20007 |      70002 |   44342 |       160547 |         5 |       245
       39 | R          | 2016-06-26 | WIN     | N          | 0-1        |    20002 |      70012 |   33523 |       160316 |        61 |       198
       40 | R          | 2016-06-26 | WIN     | N          | 2-1        |    20004 |      70011 |   56279 |       160160 |       238 |       203
       41 | R          | 2016-06-26 | WIN     | N          | 3-0        |    20003 |      70009 |   44312 |       160173 |        62 |       124
       42 | R          | 2016-06-27 | WIN     | N          | 0-4        |    20010 |      70010 |   28921 |       160062 |         3 |       133
       43 | R          | 2016-06-27 | WIN     | N          | 2-0        |    20008 |      70004 |   76165 |       160235 |        63 |       243
       44 | R          | 2016-06-28 | WIN     | N          | 1-2        |    20006 |      70001 |   33901 |       160217 |         5 |       199
       45 | Q          | 2016-07-01 | WIN     | P          | 1-1        |    20005 |      70003 |   62940 |       160316 |        58 |       181
       46 | Q          | 2016-07-02 | WIN     | N          | 3-1        |    20003 |      70001 |   45936 |       160550 |        14 |       182
       47 | Q          | 2016-07-03 | WIN     | P          | 1-1        |    20001 |      70007 |   38764 |       160163 |        63 |       181
       48 | Q          | 2016-07-04 | WIN     | N          | 5-2        |    20008 |      70008 |   76833 |       160159 |        16 |       125
       49 | S          | 2016-07-07 | WIN     | N          | 2-0        |    20004 |      70006 |   55679 |       160322 |         2 |       181
       50 | S          | 2016-07-08 | WIN     | N          | 2-0        |    20005 |      70011 |   64078 |       160160 |       126 |       275
       51 | F          | 2016-07-11 | WIN     | N          | 1-0        |    20008 |      70005 |   75868 |       160307 |       161 |       181
Sample table: referee_mast
 referee_id |      referee_name       | country_id
------------+-------------------------+------------
      70001 | Damir Skomina           |       1225
      70002 | Martin Atkinson         |       1206
      70003 | Felix Brych             |       1208
      70004 | Cuneyt Cakir            |       1222
      70005 | Mark Clattenburg        |       1206
      70006 | Jonas Eriksson          |       1220
      70007 | Viktor Kassai           |       1209
      70008 | Bjorn Kuipers           |       1226
      70009 | Szymon Marciniak        |       1213
      70010 | Milorad Mazic           |       1227
      70011 | Nicola Rizzoli          |       1211
      70012 | Carlos Velasco Carballo |       1219
      70013 | William Collum          |       1228
      70014 | Ovidiu Hategan          |       1216
      70015 | Sergei Karasev          |       1217
      70016 | Pavel Kralovec          |       1205
      70017 | Svein Oddvar Moen       |       1229
      70018 | Clement Turpin          |       1207

Sample Solution:

SQL Code:

-- Selecting the referee name and the count of matches where bookings occurred for each referee
SELECT 
    c.referee_name, -- Selecting the referee name
    COUNT(b.match_no) -- Counting the number of matches for each referee
FROM 
    player_booked a -- Selecting from the player_booked table with alias 'a'
JOIN 
    match_mast b ON a.match_no = b.match_no -- Joining with the match_mast table with alias 'b' based on match number
JOIN 
    referee_mast c ON b.referee_id = c.referee_id -- Joining with the referee_mast table with alias 'c' based on referee ID
GROUP BY 
    referee_name -- Grouping the results by referee name
HAVING 
    COUNT(b.match_no) = (
        SELECT 
            MAX(mm) -- Selecting the maximum count of matches for any referee
        FROM 
            (
                SELECT 
                    COUNT(b.match_no) AS mm -- Counting the matches for each referee
                FROM 
                    player_booked a
                JOIN 
                    match_mast b ON a.match_no = b.match_no
                JOIN 
                    referee_mast c ON b.referee_id = c.referee_id
                GROUP BY 
                    referee_name
            ) hh
    );

Sample Output:

   referee_name   | count
------------------+-------
 Mark Clattenburg |    21
(1 row)

Code Explanation:

The provided query in SQL query that retrieves player_booked table aliased as a, match_mast table aliased as b, and referee_mast table aliased as c and returns the name of the referee(s) who received the maximum number of bookings.
The JOIN keyword joins the player_booked and match_mast tables based on the match_no column, the match_mast and the referee_mast tables based on the referee_id column.
The GROUP BY statement groups the results by referee_name.
The HAVING clause filters the results to only include referees who received the maximum number of bookings.
The subquery within the HAVING clause selects the maximum number of bookings from the group of referees.
The query returns the name(s) of the referee(s) who received the maximum number of bookings.

Alternative Solutions:

Using a Window Function:

-- Selecting the referee name and the count of matches where bookings occurred for the top-ranking referee
SELECT 
    referee_name, -- Selecting the referee name
    match_count -- Selecting the count of matches where bookings occurred
FROM 
    (
        -- Subquery to calculate the count of matches and rank each referee based on the count of matches
        SELECT 
            c.referee_name, -- Selecting the referee name
            COUNT(b.match_no) AS match_count, -- Counting the number of matches for each referee
            RANK() OVER (ORDER BY COUNT(b.match_no) DESC) AS rank -- Ranking the referees based on the count of matches
        FROM 
            player_booked a -- Selecting from the player_booked table with alias 'a'
        JOIN 
            match_mast b ON a.match_no = b.match_no -- Joining with the match_mast table with alias 'b' based on match number
        JOIN 
            referee_mast c ON b.referee_id = c.referee_id -- Joining with the referee_mast table with alias 'c' based on referee ID
        GROUP BY 
            c.referee_name -- Grouping the results by referee name
    ) ranked
	-- Selecting the top-ranking referee
WHERE 
    rank = 1; 

Explanation:

This query uses a window function RANK() to assign a rank to each referee based on the count of matches. It then selects the referee with rank 1, which corresponds to the highest match count.

Using a Scalar Subquery in SELECT Clause:

-- Selecting the referee name and the count of matches where bookings occurred for the top-ranking referee
SELECT 
    referee_name, -- Selecting the referee name
    (SELECT COUNT(b.match_no) -- Subquery to count the number of matches where bookings occurred for the current referee
     FROM player_booked a -- Selecting from the player_booked table with alias 'a'
     JOIN match_mast b ON a.match_no = b.match_no -- Joining with the match_mast table with alias 'b' based on match number
     JOIN referee_mast c ON b.referee_id = c.referee_id -- Joining with the referee_mast table with alias 'c' based on referee ID
     WHERE c.referee_name = ranked.referee_name -- Filtering by the current referee name
    ) AS match_count -- Alias for the count of matches where bookings occurred
FROM 
    (
        -- Subquery to calculate the count of matches and rank each referee based on the count of matches
        SELECT 
            c.referee_name, -- Selecting the referee name
            COUNT(b.match_no) AS match_count, -- Counting the number of matches for each referee
            RANK() OVER (ORDER BY COUNT(b.match_no) DESC) AS rank -- Ranking the referees based on the count of matches
        FROM 
            player_booked a -- Selecting from the player_booked table with alias 'a'
        JOIN 
            match_mast b ON a.match_no = b.match_no -- Joining with the match_mast table with alias 'b' based on match number
        JOIN 
            referee_mast c ON b.referee_id = c.referee_id -- Joining with the referee_mast table with alias 'c' based on referee ID
        GROUP BY 
            c.referee_name -- Grouping the results by referee name
    ) ranked
	-- Selecting the top-ranking referee
WHERE 
    rank = 1; 

Explanation:

This query uses a scalar subquery in the SELECT clause to calculate the match count for each referee. It correlates the subquery with the main query using the referee name and then selects the referee with the highest match count.

Practice Online


Sample Database: soccer

soccer database relationship structure

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Find the referees and number of booked he made.
Next SQL Exercise: Find the player of each team who wear jersey number 10.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.