Leaderboard

You don't need to change anything. Just run a query when you need to knw each client's points. The query needs to count thenumber of orders placed after each client's first order and give them a point for each order. DATA TABLE: client TABLE: orders +-----------+---------+ +----------+-----------+------------+ | client_id | name | | order_id | client_id | order_date | +-----------+---------+ +----------+-----------+------------+ | 1 | Dancer | | 1 | 5 | 2022-12-01 | | 2 | Prancer | | 2 | 5 | 2022-12-02 | | 3 | Dasher | | 3 | 7 | 2022-12-03 | | 4 | Vixen | | 4 | 3 | 2022-12-04 | | 5 | Comet | | 5 | 6 | 2022-12-05 | | 6 | Cupid | | 6 | 4 | 2022-12-06 | | 7 | Donner | | 7 | 7 | 2022-12-07 | | 8 | Blitzen | | 8 | 2 | 2022-12-08 | +-----------+---------+ | 9 | 4 | 2022-12-09 | | 10 | 3 | 2022-12-10 | | 11 | 3 | 2022-12-11 | | 12 | 4 | 2022-12-12 | | 13 | 7 | 2022-12-13 | | 14 | 2 | 2022-12-14 | | 15 | 6 | 2022-12-15 | +----------+-----------+------------+ QUERY SELECT cl.name , count(order_id) as points FROM ( SELECT c.name , c.client_id , min(o.order_date) as minord FROM client c LEFT JOIN orders o USING (client_id) GROUP BY c.client_id ) cl LEFT JOIN orders o ON order_date > minord GROUP BY cl.client_id; +---------+--------+ | name | points | +---------+--------+ | Dancer | 0 | | Prancer | 7 | | Dasher | 11 | | Vixen | 9 | | Comet | 14 | | Cupid | 10 | | Donner | 12 | | Blitzen | 0 | +---------+--------+