28:"/_next/static/chunks/1tkg_z7nffgqq.js" 29:"/_next/static/chunks/06s0ttrhlu0rj.js" 2a:"/_next/static/chunks/0xffsx4xlcli_.js" 2b:"/_next/static/chunks/0h58pn99e6g4x.js" 2c:"/_next/static/chunks/2gw3dq4r2-ovd.js" 2d:I[48939,["$2","$3","$28","$29","$2a","$2b","$2c"],"MockTestView"] 2e:T53c,

A shop keeps its customers in the table customers and its orders in the table orders.

Table	Column	Meaning
`customers`	`id`	The number of the customer. It is unique.
`customers`	`name`	The name of the customer. No two customers have the same name.
`orders`	`id`	The number of the order. It is unique.
`orders`	`customer_id`	The customer who made the order.
`orders`	`total`	The value of the order, as a whole number.

Write one query that returns one row for each customer, with these columns:

name: the name of the customer.
order_count: how many orders the customer made.
total_spent: the sum of the total of those orders.

A customer with no orders must be in the result, with 0 in both numeric columns. Put the customers with more orders first. If two customers have the same number of orders, sort them by name.

2f:T444,CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers (id), total INTEGER NOT NULL ); INSERT INTO customers (id, name) VALUES (1, 'Max'), (2, 'Cy'), (3, 'Ed'), (4, 'Kai'), (5, 'Val'), (6, 'Xan'), (7, 'Hal'), (8, 'Bo'), (9, 'Sol'), (10, 'Ray'), (11, 'Lea'), (12, 'Ana'); INSERT INTO orders (id, customer_id, total) VALUES (1, 8, 107), (2, 12, 43), (3, 4, 175), (4, 2, 125), (5, 5, 24), (6, 5, 58), (7, 3, 36), (8, 12, 148), (9, 10, 56), (10, 8, 42), (11, 10, 79), (12, 8, 120), (13, 10, 86), (14, 12, 103), (15, 7, 11), (16, 3, 45), (17, 12, 5), (18, 12, 22), (19, 5, 151), (20, 8, 147), (21, 3, 51), (22, 4, 165), (23, 8, 90), (24, 8, 157), (25, 7, 67), (26, 3, 48), (27, 3, 49), (28, 10, 100), (29, 4, 179), (30, 2, 178), (31, 5, 109), (32, 4, 194), (33, 2, 93), (34, 12, 134), (35, 10, 175), (36, 5, 67), (37, 4, 148), (38, 3, 117), (39, 10, 194), (40, 12, 19); 30:T405,

A company keeps its staff in the table staff.

Column	Meaning
`id`	The number of the person. It is unique.
`name`	The name of the person.
`team`	The team of the person.
`salary`	The monthly salary in euros, as a whole number.

The company average is the average salary of all people, where each person counts once.

Write one query that returns the teams whose average salary is higher than the company average. The columns are:

team: the name of the team.
average_salary: the average salary of the team, rounded to 2 decimals with ROUND.

Put the team with the highest average_salary first. If two teams have the same average_salary, sort them by team name.

31:T689,CREATE TABLE staff ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, team TEXT NOT NULL, salary INTEGER NOT NULL ); INSERT INTO staff (id, name, team, salary) VALUES (1, 'Val', 'gamma', 7100), (2, 'Lea', 'gamma', 3200), (3, 'Zed', 'beta', 3900), (4, 'Sol', 'gamma', 6000), (5, 'Nia', 'gamma', 2500), (6, 'Bo', 'omega', 4100), (7, 'Gus', 'delta', 5200), (8, 'Uli', 'beta', 3100), (9, 'Flo', 'beta', 5000), (10, 'Kai', 'gamma', 2500), (11, 'Ivy', 'omega', 7300), (12, 'Xan', 'gamma', 2000), (13, 'Ray', 'gamma', 5600), (14, 'Oto', 'gamma', 7400), (15, 'Quin', 'omega', 3200), (16, 'Yuri', 'delta', 4700), (17, 'Wim', 'omega', 3800), (18, 'Ana', 'delta', 4800), (19, 'Tess', 'beta', 3400), (20, 'Cy', 'gamma', 3600), (21, 'Ed', 'alpha', 2500), (22, 'Pia', 'alpha', 4900), (23, 'Max', 'gamma', 5300), (24, 'Di', 'omega', 6100), (25, 'Jo', 'delta', 6400), (26, 'Hal', 'gamma', 2900), (27, 'Val1', 'beta', 2400), (28, 'Lea1', 'delta', 7800), (29, 'Zed1', 'beta', 6000), (30, 'Sol1', 'delta', 3700), (31, 'Nia1', 'beta', 4200), (32, 'Bo1', 'delta', 6700), (33, 'Gus1', 'omega', 4000), (34, 'Uli1', 'omega', 3200), (35, 'Flo1', 'gamma', 2600), (36, 'Kai1', 'alpha', 6500), (37, 'Ivy1', 'beta', 3700), (38, 'Xan1', 'omega', 5900), (39, 'Ray1', 'beta', 2700), (40, 'Oto1', 'gamma', 7900), (41, 'Quin1', 'beta', 3800), (42, 'Yuri1', 'delta', 2100), (43, 'Wim1', 'alpha', 4200), (44, 'Ana1', 'alpha', 7700), (45, 'Tess1', 'gamma', 6700), (46, 'Cy1', 'gamma', 2100), (47, 'Ed1', 'gamma', 3800), (48, 'Pia1', 'gamma', 2900), (49, 'Max1', 'delta', 7500), (50, 'Di1', 'omega', 6300); 32:T4bd,

A company keeps its staff in the table staff.

Column	Meaning
`id`	The number of the person. It is unique.
`name`	The name of the person.
`team`	The team of the person.
`salary`	The monthly salary in euros, as a whole number.

Inside each team, look at the different salary values, from the highest to the lowest. Write one query that returns every person whose salary is one of the two highest different salary values of their team.

If several people of a team have the same salary, they all have the same position. For example, if a team pays 5000, 4000, 4000 and 3000, the two highest values are 5000 and 4000. Then three people are in the result.
A team with only one salary value returns all its people.
The columns are team, name and salary.
Sort the rows by team, then by salary with the highest first, then by name.

33:T5cb,CREATE TABLE staff ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, team TEXT NOT NULL, salary INTEGER NOT NULL ); INSERT INTO staff (id, name, team, salary) VALUES (1, 'Ray', 'a', 3500), (2, 'Max', 'd', 5000), (3, 'Oto', 'd', 3200), (4, 'Yuri', 'c', 3500), (5, 'Kai', 'b', 4000), (6, 'Quin', 'd', 5000), (7, 'Nia', 'c', 4000), (8, 'Cy', 'c', 5000), (9, 'Gus', 'a', 3500), (10, 'Ana', 'c', 5000), (11, 'Pia', 'd', 4500), (12, 'Lea', 'b', 4000), (13, 'Di', 'd', 4500), (14, 'Zed', 'b', 3500), (15, 'Bo', 'b', 3200), (16, 'Sol', 'c', 3500), (17, 'Wim', 'c', 4000), (18, 'Tess', 'c', 4500), (19, 'Ed', 'c', 4000), (20, 'Hal', 'b', 4500), (21, 'Uli', 'd', 4500), (22, 'Flo', 'b', 4500), (23, 'Xan', 'b', 3200), (24, 'Val', 'c', 3200), (25, 'Jo', 'a', 4000), (26, 'Ivy', 'd', 4000), (27, 'Ray1', 'a', 5000), (28, 'Max1', 'd', 3000), (29, 'Oto1', 'b', 3200), (30, 'Yuri1', 'd', 5000), (31, 'Kai1', 'd', 5000), (32, 'Quin1', 'c', 3200), (33, 'Nia1', 'd', 5000), (34, 'Cy1', 'c', 3500), (35, 'Gus1', 'b', 5000), (36, 'Ana1', 'c', 4500), (37, 'Pia1', 'b', 5000), (38, 'Lea1', 'b', 5000), (39, 'Di1', 'd', 3200), (40, 'Zed1', 'a', 4500), (41, 'Bo1', 'a', 3500), (42, 'Sol1', 'c', 3200), (43, 'Wim1', 'c', 3200), (44, 'Tess1', 'b', 5000), (45, 'Ed1', 'a', 3200), (46, 'Hal1', 'b', 3000), (47, 'Uli1', 'd', 5000), (48, 'Flo1', 'a', 4000), (49, 'Xan1', 'c', 5000), (50, 'Val1', 'a', 4500); 1d:["$","$L2d",null,{"test":{"slug":"sql-analyst-check","title":"SQL analyst check","summary":"Three SQL problems in 60 minutes: a join, an aggregate with a subquery, and a window function.","set":"sql","setTitle":"SQL","minutes":60,"problems":[{"slug":"orders-per-customer","title":"Orders per customer","difficulty":"medium"},{"slug":"above-average-teams","title":"Teams above the average","difficulty":"medium"},{"slug":"top-two-salaries-per-team","title":"Top two salaries in each team","difficulty":"hard"}]},"lessons":[{"course":"practice-sql","courseTitle":"SQL practice","slug":"orders-per-customer","title":"Orders per customer","summary":"Count the orders of each customer, also for customers who never ordered.","minutes":20,"position":3,"total":8,"prev":{"slug":"best-contact","title":"The best way to reach a person"},"next":{"slug":"monthly-revenue","title":"Revenue for each month"},"engine":"sql","bodyHtml":"$2e","kind":"problem","files":[{"name":"query.sql","starter":"-- Run these queries to see the data. Then write your own query.\nSELECT * FROM customers;\nSELECT * FROM orders;\n","readOnly":false,"language":"sql"}],"tasks":[{"id":"t1","titleHtml":"Return the right rows for the example data.","hintHtml":"A customer with no orders must stay in the result, so use a LEFT JOIN. Count a column of orders, not *. SUM of no rows is NULL.","checks":[{"type":"sql-result","ordered":true}]},{"id":"t2","titleHtml":"Return the right rows for other data that you cannot see.","checks":[{"type":"sql-datasets","datasets":[{"setupSql":"CREATE TABLE customers (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL\n);\nCREATE TABLE orders (\n id INTEGER PRIMARY KEY,\n customer_id INTEGER NOT NULL REFERENCES customers (id),\n total INTEGER NOT NULL\n);\n\n-- This dataset has no rows.\n-- This dataset has no rows.\n"},{"setupSql":"CREATE TABLE customers (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL\n);\nCREATE TABLE orders (\n id INTEGER PRIMARY KEY,\n customer_id INTEGER NOT NULL REFERENCES customers (id),\n total INTEGER NOT NULL\n);\n\nINSERT INTO customers (id, name) VALUES\n (1, 'Ana'),\n (2, 'Bo');\n-- This dataset has no rows.\n"},{"setupSql":"CREATE TABLE customers (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL\n);\nCREATE TABLE orders (\n id INTEGER PRIMARY KEY,\n customer_id INTEGER NOT NULL REFERENCES customers (id),\n total INTEGER NOT NULL\n);\n\nINSERT INTO customers (id, name) VALUES\n (1, 'Zed'),\n (2, 'Ana'),\n (3, 'Max');\nINSERT INTO orders (id, customer_id, total) VALUES\n (1, 1, 10),\n (2, 2, 20),\n (3, 1, 30),\n (4, 2, 5),\n (5, 3, 7);\n"},{"setupSql":"CREATE TABLE customers (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL\n);\nCREATE TABLE orders (\n id INTEGER PRIMARY KEY,\n customer_id INTEGER NOT NULL REFERENCES customers (id),\n total INTEGER NOT NULL\n);\n\nINSERT INTO customers (id, name) VALUES\n (1, 'Ana'),\n (2, 'Bo'),\n (3, 'Cy');\nINSERT INTO orders (id, customer_id, total) VALUES\n (1, 1, 0),\n (2, 1, 0),\n (3, 2, 40);\n"},{"setupSql":"$2f"},{"setupSql":"CREATE TABLE customers (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL\n);\nCREATE TABLE orders (\n id INTEGER PRIMARY KEY,\n customer_id INTEGER NOT NULL REFERENCES customers (id),\n total INTEGER NOT NULL\n);\n\nINSERT INTO customers (id, name) VALUES\n (1, 'Ana');\nINSERT INTO orders (id, customer_id, total) VALUES\n (1, 1, 10),\n (2, 1, 20),\n (3, 1, 30),\n (4, 1, 40),\n (5, 1, 50),\n (6, 1, 60),\n (7, 1, 70),\n (8, 1, 80);\n"}],"ordered":true}]}],"timeoutMs":4000,"setupSql":"CREATE TABLE customers (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL\n);\nCREATE TABLE orders (\n id INTEGER PRIMARY KEY,\n customer_id INTEGER NOT NULL REFERENCES customers (id),\n total INTEGER NOT NULL\n);\n\nINSERT INTO customers (id, name) VALUES\n (1, 'Ana'),\n (2, 'Bo'),\n (3, 'Cy'),\n (4, 'Di');\nINSERT INTO orders (id, customer_id, total) VALUES\n (1, 1, 50),\n (2, 1, 30),\n (3, 2, 20),\n (4, 1, 10),\n (5, 3, 80),\n (6, 3, 15);\n","solutionSql":"SELECT c.name AS name,\n COUNT(o.id) AS order_count,\n COALESCE(SUM(o.total), 0) AS total_spent\nFROM customers AS c\nLEFT JOIN orders AS o ON o.customer_id = c.id\nGROUP BY c.id, c.name\nORDER BY order_count DESC, name ASC;\n","problem":{"set":"sql","setTitle":"SQL","difficulty":"medium","topics":["joins","aggregation"],"samples":1,"hidden":6}},{"course":"practice-sql","courseTitle":"SQL practice","slug":"above-average-teams","title":"Teams above the average","summary":"Find the teams that pay more than the company average.","minutes":20,"position":5,"total":8,"prev":{"slug":"monthly-revenue","title":"Revenue for each month"},"next":{"slug":"running-total-per-store","title":"Running total per store"},"engine":"sql","bodyHtml":"$30","kind":"problem","files":[{"name":"query.sql","starter":"-- Run this query to see the data. Then write your own query.\nSELECT * FROM staff;\n","readOnly":false,"language":"sql"}],"tasks":[{"id":"t1","titleHtml":"Return the right rows for the example data.","hintHtml":"Group by team to get each average. Use HAVING to compare it with the average of all staff, which a subquery in brackets can give you.","checks":[{"type":"sql-result","ordered":true}]},{"id":"t2","titleHtml":"Return the right rows for other data that you cannot see.","checks":[{"type":"sql-datasets","datasets":[{"setupSql":"CREATE TABLE staff (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n team TEXT NOT NULL,\n salary INTEGER NOT NULL\n);\n\n-- This dataset has no rows.\n"},{"setupSql":"CREATE TABLE staff (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n team TEXT NOT NULL,\n salary INTEGER NOT NULL\n);\n\nINSERT INTO staff (id, name, team, salary) VALUES\n (1, 'Ana', 'solo', 4000),\n (2, 'Bo', 'solo', 5000);\n"},{"setupSql":"CREATE TABLE staff (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n team TEXT NOT NULL,\n salary INTEGER NOT NULL\n);\n\nINSERT INTO staff (id, name, team, salary) VALUES\n (1, 'Ana', 'alpha', 10000),\n (2, 'Cy', 'beta', 5000),\n (3, 'Di', 'beta', 5000),\n (4, 'Ed', 'beta', 5000),\n (5, 'Flo', 'beta', 5000),\n (6, 'Gus', 'beta', 5000),\n (7, 'Hal', 'beta', 5000),\n (8, 'Ivy', 'beta', 5000),\n (9, 'Jo', 'beta', 5000),\n (10, 'Kai', 'beta', 5000),\n (11, 'Lea', 'beta', 5000),\n (12, 'Zed', 'gamma', 6000);\n"},{"setupSql":"CREATE TABLE staff (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n team TEXT NOT NULL,\n salary INTEGER NOT NULL\n);\n\nINSERT INTO staff (id, name, team, salary) VALUES\n (1, 'Ana', 'north', 3000),\n (2, 'Bo', 'north', 3001),\n (3, 'Cy', 'north', 3001),\n (4, 'Di', 'south', 1000),\n (5, 'Ed', 'south', 1000),\n (6, 'Flo', 'south', 1001),\n (7, 'Gus', 'east', 2000);\n"},{"setupSql":"CREATE TABLE staff (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n team TEXT NOT NULL,\n salary INTEGER NOT NULL\n);\n\nINSERT INTO staff (id, name, team, salary) VALUES\n (1, 'Ana', 'red', 5000),\n (2, 'Bo', 'blue', 5000),\n (3, 'Cy', 'green', 2000),\n (4, 'Di', 'amber', 5000),\n (5, 'Ed', 'green', 2000),\n (6, 'Flo', 'amber', 5000);\n"},{"setupSql":"$31"}],"ordered":true}]}],"timeoutMs":4000,"setupSql":"CREATE TABLE staff (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n team TEXT NOT NULL,\n salary INTEGER NOT NULL\n);\n\nINSERT INTO staff (id, name, team, salary) VALUES\n (1, 'Ana', 'sales', 3000),\n (2, 'Bo', 'sales', 3200),\n (3, 'Cy', 'dev', 5200),\n (4, 'Di', 'dev', 4800),\n (5, 'Ed', 'dev', 5000),\n (6, 'Flo', 'support', 3600),\n (7, 'Gus', 'support', 3400),\n (8, 'Hal', 'ops', 6000);\n","solutionSql":"SELECT team, ROUND(AVG(salary), 2) AS average_salary\nFROM staff\nGROUP BY team\nHAVING AVG(salary) > (SELECT AVG(salary) FROM staff)\nORDER BY average_salary DESC, team ASC;\n","problem":{"set":"sql","setTitle":"SQL","difficulty":"medium","topics":["aggregation","subqueries"],"samples":1,"hidden":6}},{"course":"practice-sql","courseTitle":"SQL practice","slug":"top-two-salaries-per-team","title":"Top two salaries in each team","summary":"Find the best paid people of each team, and keep ties together.","minutes":30,"position":7,"total":8,"prev":{"slug":"running-total-per-store","title":"Running total per store"},"next":{"slug":"who-works-below","title":"Who works below the head"},"engine":"sql","bodyHtml":"$32","kind":"problem","files":[{"name":"query.sql","starter":"-- Run this query to see the data. Then write your own query.\nSELECT * FROM staff;\n","readOnly":false,"language":"sql"}],"tasks":[{"id":"t1","titleHtml":"Return the right rows for the example data.","hintHtml":"A window function with PARTITION BY team can give each person a position. Use DENSE_RANK, so that people with the same salary share a position. Filter on the position in an outer query.","checks":[{"type":"sql-result","ordered":true}]},{"id":"t2","titleHtml":"Return the right rows for other data that you cannot see.","checks":[{"type":"sql-datasets","datasets":[{"setupSql":"CREATE TABLE staff (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n team TEXT NOT NULL,\n salary INTEGER NOT NULL\n);\n\n-- This dataset has no rows.\n"},{"setupSql":"CREATE TABLE staff (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n team TEXT NOT NULL,\n salary INTEGER NOT NULL\n);\n\nINSERT INTO staff (id, name, team, salary) VALUES\n (1, 'Ana', 'a', 3000),\n (2, 'Bo', 'b', 2000),\n (3, 'Cy', 'c', 1000);\n"},{"setupSql":"CREATE TABLE staff (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n team TEXT NOT NULL,\n salary INTEGER NOT NULL\n);\n\nINSERT INTO staff (id, name, team, salary) VALUES\n (1, 'Ana', 'a', 3000),\n (2, 'Bo', 'a', 3000),\n (3, 'Cy', 'a', 3000),\n (4, 'Di', 'a', 3000);\n"},{"setupSql":"CREATE TABLE staff (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n team TEXT NOT NULL,\n salary INTEGER NOT NULL\n);\n\nINSERT INTO staff (id, name, team, salary) VALUES\n (1, 'Ana', 'a', 5000),\n (2, 'Bo', 'a', 4000),\n (3, 'Cy', 'a', 3000),\n (4, 'Di', 'a', 2000);\n"},{"setupSql":"CREATE TABLE staff (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n team TEXT NOT NULL,\n salary INTEGER NOT NULL\n);\n\nINSERT INTO staff (id, name, team, salary) VALUES\n (1, 'Ana', 'a', 5000),\n (2, 'Bo', 'a', 4000),\n (3, 'Cy', 'a', 4000),\n (4, 'Di', 'a', 3000),\n (5, 'Ed', 'b', 7000),\n (6, 'Flo', 'b', 7000),\n (7, 'Gus', 'b', 6000),\n (8, 'Hal', 'b', 6000);\n"},{"setupSql":"$33"}],"ordered":true}]}],"timeoutMs":4000,"setupSql":"CREATE TABLE staff (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n team TEXT NOT NULL,\n salary INTEGER NOT NULL\n);\n\nINSERT INTO staff (id, name, team, salary) VALUES\n (1, 'Ana', 'sales', 3000),\n (2, 'Bo', 'sales', 3200),\n (3, 'Cy', 'sales', 3200),\n (4, 'Di', 'sales', 2900),\n (5, 'Ed', 'dev', 5200),\n (6, 'Flo', 'dev', 4800),\n (7, 'Gus', 'dev', 5000),\n (8, 'Hal', 'dev', 4800),\n (9, 'Ivy', 'support', 3600);\n","solutionSql":"SELECT team, name, salary\nFROM (\n SELECT team, name, salary,\n DENSE_RANK() OVER (PARTITION BY team ORDER BY salary DESC) AS position\n FROM staff\n)\nWHERE position <= 2\nORDER BY team ASC, salary DESC, name ASC;\n","problem":{"set":"sql","setTitle":"SQL","difficulty":"hard","topics":["window-functions"],"samples":1,"hidden":6}}],"pages":{"js":"/sandbox/js/3fcbbca1a45e.html","py":"/sandbox/py/9ef6e18dbbc6.html","sql":"/sandbox/sql/b8f2ec308c3f.html","web":"/sandbox/web/1e0188ea18a5.html","pw":"/sandbox/pw/2724b833599a.html"}}]