>_ IzzyLab

Hands-on task

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

SQLite: common table expression (CTE)

medium~7 min

The database /root/pay.db has a table payments(user TEXT, amount INTEGER) with payments. You need to find "VIP" users whose total payment amount is strictly greater than 100. Write a query with a common table expression (WITH ... AS (...)): first, in an auxiliary table, compute the sum for each user, then select from it those whose sum > 100. Write the names to /root/vip.txt (one per line).

A query with a CTE is easier to write over several lines: type it into the file /root/vip.sql in the Editor tab and run it in sqlite3.

What you'll practise:
  • cte
  • with
  • aggregation
  • group by
  • filtering by aggregate

Topic breakdown

A common table expression (CTE) is a named subquery declared via WITH <name> AS (...) before the main query; it is then referred to by name like a regular table. This technique is handy when you need to filter data by an aggregate: aggregate functions can't be used in WHERE, so the sum is first computed in a nested step (GROUP BY + an aggregate with an alias), and the outer query then filters the ready result. An alternative to a CTE for filtering by an aggregate is HAVING, but WITH makes multi-step logic more readable.

What's checked

  1. the report has exactly the VIP users
  2. the WITH query is saved in /root/vip.sql

What it looks like