codejump
MediumPro challengeSQL

Upsert a Page View Counter

SQLDatabasesUpsert

Table: page_views (page is the primary key)

Recording a page view should increment the existing counter if the page
has been seen before, or insert a new row starting at 1 if it hasn't —
in a single atomic statement, not a separate "check then insert-or-update"
round trip.

Write a statement that records one view for '/home', then select the
final state of the table.

Expected output (columns: page, views), ordered by page:

  • If /home already had 10 views → /home|11
  • If /home didn't exist yet (only some other page did) → that other page

unchanged, plus a new /home|1 row

Hint — INSERT ... ON CONFLICT(page) DO UPDATE SET views = views + 1

is a single statement: SQLite attempts the insert, and only runs the

DO UPDATE clause if it collides with an existing primary key.

Sample tests

Test #1Existing page — counter increments
Input: "CREATE TABLE page_views (page TEXT PRIMARY KEY, views INTEGER);\nINSERT INTO page_views VALUES ('/home', 10);"
Output: "/home|11"
Test #2New page — inserted at 1, unrelated page untouched
Input: "CREATE TABLE page_views (page TEXT PRIMARY KEY, views INTEGER);\nINSERT INTO page_views VALUES ('/about', 0);"
Output: "/about|0\n/home|1"
← All challenges