Fun interview question: How to update Saint Quartz stones with one line SQL statement in FGO?
Tags: sql, mysql, interview
Published: 2020-02-22 16:37:17

---
title: Fun interview question: How to update Saint Quartz stones with one line SQL statement in FGO?
published: true
description: A fun interview question from a nerdy interviewer.
tags: SQL, MySQL, Interview
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/7j1jmmaylz8bcqcsx3a2.png
---

So recently I have been obsessed with the mobile game [Fate/Grand Zero](https://fate-go.us/)(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. 

![Time to summon a spirit!](https://dev-to-uploads.s3.amazonaws.com/i/tpmztuljygl03ciwr33e.png)

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.

```sql
/*
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`.

```sql
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](https://www.db-fiddle.com/f/szE1s5yyUVMsBsQvqU7cvU/0).