Investment platform: clients, accounts and orders¶

SQL exercises · Part I

Problem¶

A database of an investment platform: clients hold one or more accounts, and every order buys a quantity of an asset (stock, bond, crypto…) on an account. Write four queries: clients with large orders, crypto bought by high-risk clients, the number of orders of each client, and the clients with the most orders.

Schema and test data¶

-- The exercise does not give the schema: these tables contain exactly the
-- columns that the queries use.
CREATE TABLE clienti (
    id_cliente      INTEGER PRIMARY KEY,
    nome            TEXT,
    email           TEXT,
    profilo_rischio TEXT
);
CREATE TABLE conti (
    id_conto   INTEGER PRIMARY KEY,
    id_cliente INTEGER NOT NULL REFERENCES clienti(id_cliente)
);
CREATE TABLE assets (
    id_asset   INTEGER PRIMARY KEY,
    ticker     TEXT,
    nome_asset TEXT,
    categoria  TEXT
);
CREATE TABLE ordini (
    id_ordine INTEGER PRIMARY KEY,
    id_conto  INTEGER NOT NULL REFERENCES conti(id_conto),
    id_asset  INTEGER NOT NULL REFERENCES assets(id_asset),
    quantita  INTEGER
);

-- Test data (not part of the exercise): written to exercise the queries,
-- including a client with no account, an account with no orders and a tie.
INSERT INTO clienti VALUES
    (1, 'Anna',  'anna@example.com',  'alto'),
    (2, 'Bruno', 'bruno@example.com', 'basso'),
    (3, 'Carla', 'carla@example.com', 'alto'),
    (4, 'Dario', 'dario@example.com', 'medio');   -- no account at all
INSERT INTO conti VALUES (10, 1), (11, 1), (12, 2), (13, 3);   -- 13: no orders
INSERT INTO assets VALUES
    (100, 'BTC',  'Bitcoin',   'Crypto'),
    (101, 'ETH',  'Ethereum',  'Crypto'),
    (102, 'AAPL', 'Apple',     'Equity'),
    (103, 'BTP',  'BTP 2030',  'Bond');
INSERT INTO ordini VALUES
    (1000, 10, 100, 50),
    (1001, 11, 102, 10),
    (1002, 10, 101, 45),
    (1003, 12, 102, 60),
    (1004, 12, 103, 5),
    (1005, 12, 100, 20);

The exercise names the tables but does not define them: the schema contains the columns the queries use. The test data are mine, written to try the queries, not part of the exercise.

Queries¶

Query 1 · Clients with an order above 40 units¶

Return ID, name and email of the clients with at least one order of more than 40 units, ordered by ID.

SELECT DISTINCT c1.id_cliente, c1.nome, c1.email
FROM clienti AS c1
JOIN conti AS c2
ON c1.id_cliente = c2.id_cliente
JOIN ordini AS c3
ON c2.id_conto = c3.id_conto

WHERE c3.quantita > 40
ORDER BY c1.id_cliente

DISTINCT matters: a client with two large orders would otherwise appear twice.

Query 2 · Crypto assets bought by high-risk clients¶

Return ticker, name and category of the assets in category 'Crypto' bought by clients with risk profile 'alto', with the quantity ordered, ordered by ticker.

SELECT c4.ticker, c4.nome_asset, c4.categoria, c3.quantita
FROM clienti AS c1
JOIN conti AS c2
ON c1.id_cliente = c2.id_cliente
JOIN ordini AS c3
ON c2.id_conto = c3.id_conto
JOIN assets AS c4
ON c3.id_asset = c4.id_asset
WHERE c4.categoria = 'crypto' AND c1.profilo_rischio = 'alto'

The query returns no rows on the test data.

Query 3 · Number of orders of each client¶

For every client, return ID, name, email and the total number of orders, showing 0 for clients who never ordered; order by ID.

SELECT c1.id_cliente, c1.nome, c1.email, COUNT(c3.id_ordine) AS numero_totale_ordini
FROM clienti AS c1
LEFT JOIN conti AS c2
ON c1.id_cliente = c2.id_cliente
LEFT JOIN ordini AS c3
ON c2.id_conto = c3.id_conto
GROUP BY c1.id_cliente, c1.nome, c1.email
ORDER BY c1.id_cliente

Both joins must be LEFT JOIN, and the count must be on c3.id_ordine: COUNT(*) would give 1 to a client without orders, because the outer join still produces one row for them.

Query 4 · Clients with the most orders¶

Return ID, name and email of the client (or clients, in case of a tie) with the largest number of orders, ordered by ID.

-- first query
CREATE VIEW conteggio_ordini_clienti AS
SELECT c1.id_cliente, c1.nome, c1.email, COUNT(c3.id_ordine) AS numero_ordini
FROM clienti AS c1
LEFT JOIN conti AS c2
ON c1.id_cliente = c2.id_cliente
LEFT JOIN ordini AS c3
ON c2.id_conto = c3.id_conto
GROUP BY c1.id_cliente, c1.nome, c1.email;

-- second query
SELECT id_cliente, nome, email
FROM conteggio_ordini_clienti
WHERE numero_ordini = (SELECT MAX(numero_ordini) FROM conteggio_ordini_clienti);

The view counts the orders once; the outer query compares each client with the maximum of that count, so ties are kept.

Takeaways¶

• Counting with an outer join: count a column of the optional table, not *.
• "The maximum, ties included" is a comparison with (SELECT MAX(...)), not ORDER BY ... LIMIT 1.
• String literals must match the data exactly where comparison is case-sensitive.