Fun interview question: How to update Saint Quartz stones with one line SQL statement in FGO?

So recently I have been obsessed with the mobile game Fate/Grand Zero(FGO) and spent lots of time probing into every detail of the game. One particular detail that jumps out right in front of me is how the Summoning works. It also becomes one of my fun interview questions that challenge candidates' SQL skills.

In FGO, a player holds two types of "Saint Quartz stones". The first type is Free Saint Quartz which is usually awarded for achievements such as finishing certain levels or daily logging-in to the game. The second type is Paid Saint Quartz which is, as the name has implied, purchased by the player. The player can either pays 3 stones to summon one time or 30 stones, for higher probabilities of getting rare characters, to summon 10 times. The rules of how they are spent are:

1. Free Saint Quartz stones will be spent first.
2. If Free Saint Quartz stones are not enough for summoning, Paid Saint Quartz stones will be spent then for the remaining part.

For example, if the player holds 40 Free Saint Quartz stones and 20 Paid Saint Quartz stones, after 10-time summoning, the numbers of stones are 10 and 20 respectively. And if the player holds 15 free and 20 paid stones, the results are 0 and 5.

The interview question is: given that a table player contains two columns free_sant_quartz and paid_sant_quartz, both in integer, write a SQL statement according to the rules with only one UPDATE statement. (Assuming that the DB system is MySQL or MariaDB)

Before we get to the real UPDATE answer, let us take a look at the SELECT statement that allows the player to preview how many remaining stones there would be after 10-time summoning.

/*
CREATE TABLE IF NOT EXISTS players (
  id int(6) NOT NULL,
  free_sant_quartz int(3) NOT NULL,
  paid_sant_quartz int(3) NOT NULL,
  PRIMARY KEY (id)
);
*/

SELECT 
GREATEST(free_sant_quartz - 30, 0) AS free_sant_quartz_remaining, 
LEAST(free_sant_quartz - 30, 0) + paid_sant_quartz AS paid_sant_quartz_remaining
FROM players;
-- The WHERE clause is omitted

The GREATEST() and LEAST() are two very useful functions to solve the problem as they simulate the "priorities" between free_sant_quartz and paid_sant_quartz. If the player holds 30 Free Sant Quartz stones or more, the result of GREATEST(free_sant_quartz - 30, 0) will simply be the remaining Free Sant Quartz stones and the clause LEAST(free_sant_quartz - 30, 0) produces zero. And if the player holds less than 30, the former produces zero and the latter produces a negative number so paid_sant_quartz gets deducted for the remaining part.

Once we get the SELECT statement, it becomes easy to come up with an UPDATE statement for the problem. The only catch is that we need a temporary variable for the deducted free_sant_quartz.

UPDATE players SET 
free_sant_quartz = GREATEST(@temp := free_sant_quartz - 30, 0),
paid_sant_quartz = LEAST(@temp, 0) + paid_sant_quartz
WHERE free_sant_quartz + paid_sant_quartz >= 30 AND player_id = ?;
-- The player_id is omitted. 

Case closed.

You can play with the complete example at DB Fiddle.