>_ IzzyLab

Hands-on task

This task is step 16 of the course “SQL with SQLite”

SQLite: ranking with a window function

medium~7 min

The database /root/game.db has a table scores(player TEXT, points INTEGER) with players' results. Write a query with the window function ROW_NUMBER() (or RANK()) that assigns each player a place in the tournament by descending points, and write the result to /root/rank.txt in the format player|place. The player with the most points must have place 1.

What you'll practise:
  • window functions
  • row_number
  • ranking
  • order by
  • sqlite3

Topic breakdown

Window functions compute a value for each row by "peeking" at neighboring rows within the window but, unlike GROUP BY, do not collapse the set — just as many rows remain. Numbering functions (ROW_NUMBER(), RANK()) always go paired with OVER (...), where ORDER BY defines the sort order before numbers are assigned. The sort direction (ASC/DESC) determines who gets place 1. The sqlite3 utility separates columns with the | character by default, and output is easily redirected to a file with >.

What's checked

  1. every player has the right place

What it looks like