Hands-on task
SQLite: common table expression (CTE)
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.
- cte
- with
- aggregation
- group by
- filtering by aggregate