Full Solution to Lost at SQL – Learn SQL Game

A game is a fun way to learn SQL. Let’s take a look at a full solution to one of the most popular SQL games, Robin Lord’s Lost at SQL!

SQL games are a great interactive way to practice your SQL skills. If you are bored with video courses and textbooks, a game can help you solidify and even learn SQL. Lost at SQL is one of the most popular SQL games. In this article, we will take a look at the full solution to all chapters. The game is quite large (it has 20 chapters and 2 endings) and it can become complicated quite quickly. Because using hints reduces your final score, you can follow along with this article while playing the game.

If you are looking for another interactive way to learn SQL, consider our Monthly SQL Practice track, where we publish a new SQL practice course each month.

Lost at SQL – Full Game Solution

As you start the game, you’ll be asked to enter your player name and choose whether you want to play the Story mode or tackle advanced SQL challenges. We’ll tackle the story mode.

Lost at SQL is organized into chapters. At the start of each chapter, the game will type out a piece of the story. You can control the speed of the typing (as well as other useful parameters) in the Settings menu (the gear icon on the top left of the screen).

The Answer section is collapsed by default. Click the button labeled “Answer” to open the query window.

You might want to keep our SQL Basics Cheat Sheet around for this game, as the queries get quite long. Let’s start!

Chapter 1: Trapped Beneath the Waves

In the first chapter, we are introduced to the setting of the game. You are a captain trapped in a sinking submarine, and your only method of communication is a computer terminal. Will you be able to repair the submarine? Let’s find out.

Task:

Get all the issues from the malfunctions table.

Solution:

SELECT issues 
FROM malfunctions;

Chapter 2: How to Fix the Malfunctions

Great! We have found the list of malfunctions. Now it’s time to find the methods to fix them.

Task:

Using the malfunctions table again, get the issues and fix columns.

Solution:

SELECT issues, fix 
FROM malfunctions;

Chapter 3: The Status of the Crew

Now that we understand the situation of the sub, we have to find out the status of our crew.

Task:

Get all the columns from the crew table.

Solution:

SELECT * 
FROM crew;

Chapter 4: Find the First Officer

The remaining crew list does not look too promising. You should probably contact your first officer.

Task:

Either:

1. Get all of the columns from the crew table where the role is 'first officer', or
2. Get all of the columns from the crew table where staff_name is 'Helga Sinclair'.

Solution 1:

SELECT * 
FROM crew 
WHERE role = 'first officer';

Solution 2:

SELECT * 
FROM crew 
WHERE staff_name = 'Helga Sinclair';

Chapter 5: Find the Functional Pods

Good news: Helga is alive! Time to find all pods suitable for your escape.

Task:

Get all of the columns from the pods_list table where status is 'functioning', and range is more than 1500.

Solution:

SELECT * 
FROM pods_list 
WHERE range > 1500 
AND status = 'functioning';

Chapter 6: Pods Needing Attention

Now you will have to coordinate with Helga’s team to repair the escape pods’ circuits. Which ones need attention?

Task:

You need to find all of the circuits where the area is ‘pod 03’, OR status is NOT ‘green’.

Solution:

SELECT * 
FROM circuits 
WHERE area = 'pod 03' 
OR NOT status = 'green';

Chapter 7: Crew Locations

Not all crew have made it to the escape pods yet. Helga needs help organizing search parties and needs you to compile a list of locations.

Task:

You need to find out how many of the crew are in different places. Count the staff_name column grouped by the last_location column. Call your new staff count column crew_count.

Solution:

SELECT 
last_location, 
COUNT(staff_name) AS crew_count 
FROM crew 
GROUP BY last_location;

Chapter 8: Crew Status and Location

Time is running low and you need to prioritize the injured crew. You need to modify the search parameters.

Task:

Find out how many of the crew are in different places (last_location) AND in different states of health (i.e. the status column). Group by the last_location column and the status column. Count the staff_name column and call it crew_count.

Solution:

SELECT 
last_location, 
status, 
COUNT(staff_name) AS crew_count 
FROM crew 
GROUP BY 
last_location, 
status;

Chapter 9: Check the Pod Groups

You hear rescuers trying to get to you, but you choose your crew over yourself and order them to focus on saving the others. From your computer terminal, you need to find out how to group the people into pods in the most effective way.

Task:

You need to group your crew based on:

How far the pods are from groups of crew (based on location), and

The total weight of each group (some pods have a higher weight limit).

Group everyone by pod group, but only include crew members whose status is not 'deceased'. Sum each group’s combined weight and call it total_weight. Finally, find the maximum distance from the group to the pod and call it max_distance.

Solution:

SELECT 
pod_group, 
SUM(weight_kg) AS total_weight, 
MAX(distance_to_pod) AS max_distance 
FROM crew 
WHERE NOT status = 'deceased' 
GROUP BY pod_group;

Chapter 10: Check the Weights

There has been a mistake when recording the weights of the crew – some weights have lost a zero! You need to find these issues as soon as possible.

Task:

You need to see if any of the crew are suspiciously light. Select the name and weight for your crew and order the list by weight from low to high.

Solution:

SELECT 
staff_name, 
weight_kg 
FROM crew
ORDER BY weight_kg;

Chapter 11: Fix the Crew Weight

You were right; some of the crew members’ weights are not accurate. This has to be fixed or the pods risk overloading!

Task:

You need to fix the incorrect weights for the crew members. Select the name and weight for your crew and create a new column called fixed_weight. When the current weight column is over 10, then fixed_weight should match it exactly. But if it is less than 10, then the fixed_weight column should show the weight number multiplied by 10. Order your results by weight from lowest to highest to check that your fixed_weight column is working as you'd expect.

Solution:

SELECT 
staff_name, 
weight_kg, 
CASE 
WHEN weight_kg > 10 THEN weight_kg 
ELSE weight_kg * 10 
END AS fixed_weight 
FROM crew 
ORDER BY weight_kg;

Chapter 12: Find the Overloaded Pods

This task is composed of a few sub-tasks. Enter and submit each solution in order, then click a button to combine all of the subtasks and create the final query.

Task:

You need to add together all the steps from the last few levels:

• Filtering for crew mates that aren't deceased.
• Correcting weights.
• Grouping by pod group and summing combined weights.
• Filtering groups to use the ones where total weight is more than 1000 and ordering by total weight from highest to lowest.

Note: In all multi-step queries, you can avoid having to re-type your queries in the final solution by choosing the Get step queries button and then running the query.

Step 1 - Filtering crew mates:

SELECT 
staff_name, 
pod_group, 
weight_kg 
FROM crew 
WHERE NOT status = 'deceased';

Step 2 - Correcting weights:

SELECT
staff_name,
pod_group,
CASE
		WHEN weight_kg > 10 THEN weight_kg
		ELSE weight_kg * 10
END AS fixed_weight
FROM filtered_crew;

Step 3 - Summing group weights:

SELECT 
pod_group, 
SUM(fixed_weight) AS total_weight 
FROM fixed_crew 
GROUP BY pod_group;

Step 4 - Filtering groups by weight:

SELECT * 
FROM grouped_crew 
WHERE total_weight > 1000 
ORDER BY total_weight DESC;

Full Solution:

WITH filtered_crew AS (
	SELECT
  		staff_name,
  		pod_group,
  		weight_kg
	FROM crew
	WHERE NOT status = 'deceased'
), fixed_crew AS (
	SELECT
  		staff_name,
  		pod_group,
  	CASE
    		WHEN weight_kg > 10 THEN weight_kg
    		ELSE weight_kg * 10
  	END AS fixed_weight
	FROM filtered_crew
), grouped_crew AS (
	SELECT
  		pod_group,
  		SUM(fixed_weight) AS total_weight
	FROM fixed_crew
	GROUP BY pod_group
)
SELECT *
FROM grouped_crew
WHERE total_weight > 1000
ORDER BY total_weight DESC;

Chapter 13: Has Everyone Boarded?

Helga needs help checking if everyone has boarded a pod. Your terminal is the best solution for this.

Task:

Get a list of all the staff names where party_status is not 'boarded'. You will need to use JOIN to combine the staff names from the crew table and the party_status from the evacuation_groups table.

Solution:

SELECT
staff_name,
party_status
FROM crew
JOIN evacuation_groups 
ON crew.pod_group = evacuation_groups.pod_group
WHERE NOT party_status = 'boarded';

Chapter 14: Missing Crew

Some escape pods went missing. You need to find out if some crew have disappeared in them.

Task:

Get a list of all the staff who are in the original_crew table but who aren't in the crew table.

Step 1 - Joining the tables:

SELECT *
FROM original_crew
LEFT JOIN crew 
ON crew.staff_id = original_crew.staff_id;

Step 2 - Filtering the crew:

SELECT * 
FROM joined_crew 
WHERE last_location IS NULL;

Full Solution:

WITH joined_crew AS (
	SELECT *
	FROM original_crew
  	LEFT JOIN crew on crew.staff_id = original_crew.staff_id
)
SELECT *
FROM joined_crew
WHERE last_location IS NULL;

Chapter 15: Suspect Crew

Well, there is no escape for you – but you can still find out who was behind the explosion.

Task:

Get a list of all the staff in the full_crew table who don't have a current_location and whose role history doesn't end with 'Transfer' and doesn't contain 'Injured'.

Step 1 - Getting lists of roles:

SELECT
staff_name,
GROUP_CONCAT(role) as combined_roles
FROM staffing_changes
GROUP BY staff_name;

Step 2 - Joining the tables:

SELECT *
FROM full_crew
FULL OUTER JOIN grouped_changes 
ON full_crew.staff_name = grouped_changes.staff_name;

Step 3 - Filtering by location and role:

SELECT *
FROM joined_crew
WHERE last_location IS NULL
AND NOT combined_roles like '%Transfer'
AND NOT combined_roles like '%Injured%';

Full Solution:

WITH grouped_changes AS (
	SELECT
  		staff_name,
  		GROUP_CONCAT (role) as combined_roles
	FROM staffing_changes
	GROUP BY staff_name
), joined_crew AS (
	SELECT *
	FROM 	full_crew
  	FULL OUTER JOIN grouped_changes 
ON full_crew.staff_name = grouped_changes.staff_name
)
SELECT *
FROM joined_crew
WHERE last_location IS NULL
AND NOT combined_roles like '%Transfer'
AND NOT combined_roles like '%Injured%';

Chapter 16: Covering Other Cases

The results of that query weren’t helpful. Maybe you inadvertently left out some possible cases. Let’s modify the query.

Task:

Get a list of all the staff in the joined_crew list who don't have a last_location and whose role history doesn't end with 'Transfer'. Exclude anyone whose role history contains 'Injured' – but only if that role history doesn't include 'Returned' afterwards.

Solution:

SELECT *
FROM joined_crew
WHERE last_location IS NULL
AND NOT combined_roles like '%Transfer'
AND (
(NOT combined_roles like '%Injured%')
		OR combined_roles like '%Injured%Returned%'
  	);

Chapter 17: Explosives Depot Visits

One more question to answer. There couldn’t have been any external explosion, so who set off an explosion inside the sub?

Task:

You have a list of times people visited each of the explosives depots. You need to find the last time a person visited each store. In your result include the columns staff_name, staff_id, depot, and timestamp.

Step 1 - Ordering the visits:

SELECT 
*,
ROW_NUMBER() OVER (
PARTITION BY depot 
ORDER BY timestamp DESC
  	) AS reverse_ordered
FROM depot_records;

Step 2 - Finding the last visits:

SELECT
staff_name,
staff_id,
depot,
timestamp
FROM found_last
WHERE reverse_ordered = 1;

Full Solution:

WITH found_last AS (
	SELECT
*,
  		ROW_NUMBER() OVER (
    			PARTITION BY depot
    			ORDER BY timestamp DESC
  		) AS reverse_ordered
	FROM depot_records
)
SELECT
staff_name,
staff_id,
depot,
timestamp
FROM found_last
WHERE reverse_ordered = 1;

Chapter 18: Call Logs

It was sabotage! Now you have to find out how the spy contacted an outsider to organize this.

Task:

You have a list of call logs. Figure out which number called your spy and one other ID on the crew. Exclude any calls where the max call duration was 1.

Part 1 - Getting call info:

SELECT 
*,
strftime ('%s', start_time) AS start_seconds,
strftime ('%s', end_time) AS end_seconds
FROM phone_logs;

Part 2 - Finding call duration:

SELECT
*,
end_seconds - start_seconds AS duration
FROM date_to_seconds;

Part 3 - Filtering the calls:

SELECT phone_number
FROM calculated_duration
WHERE incoming_outgoing = 'Incoming'
AND staff_id = 'mm833'
AND duration > 1;

Part 4 - Getting relevant logs:

SELECT *
FROM phone_logs
WHERE phone_number IN suspect_numbers
AND staff_id IS NOT 'mm833';

Part 5 - Deduplicating values:

SELECT DISTINCT
staff_name,
phone_number
FROM suspect_individuals;

Part 6 - Counting who made calls:

SELECT 
*,
  	COUNT(staff_name) OVER (
		PARTITION BY phone_number
  	) AS staff_count
FROM distinct_calls;

Part 7 - Final filtering:

SELECT staff_name
FROM counted_staff
WHERE staff_count = 1;

Full Solution:

WITH date_to_seconds AS (
	SELECT 
*,
  		strftime('%s', start_time) AS start_seconds,
  		strftime('%s', end_time) AS end_seconds
	FROM phone_logs
), calculated_duration AS (
	SELECT
  		*,
  		end_seconds - start_seconds AS duration
	FROM date_to_seconds
), suspect_numbers AS (
	SELECT phone_number
	FROM 	calculated_duration
	WHERE incoming_outgoing = 'Incoming'
  		AND staff_id = 'mm833'
  		AND duration > 1
), suspect_individuals AS (
	SELECT *
	FROM phone_logs
	WHERE phone_number IN suspect_numbers
  		AND staff_id IS NOT 'mm833'
), distinct_calls AS (
	SELECT DISTINCT
  		staff_name,
  		phone_number
	FROM suspect_individuals
), counted_staff as (
	SELECT
  		*,
  		COUNT(staff_name) OVER (
    			PARTITION BY phone_number
  		) AS staff_count
	FROM distinct_calls
)
SELECT staff_name
FROM counted_staff
WHERE staff_count = 1;

Chapter 19: Find the Lifts

You know who sabotaged the ship. But you also know that you have to escape to the bridge right now. Time to find all of the working lifts – and quickly.

Task:

Find all of the lifts that:

• Are below Deck 2.
• Don't have short circuits and flooding at the same time.
• Don't have a loss of oxygen or a broken drive shaft.

In your result, include the columns lift_name, deck, and noisy (where noisy is 1 for lifts with a lubricant leak and 0 for non-leaking lifts).

Part 1 - Finding the lifts:

SELECT
time,
lift_name,
deck
FROM lift_locations
UNION ALL
SELECT
timestamp,
lift_name,
location
FROM lift_locations_2;

Part 2 - Ranking lifts by time:

SELECT
*,
ROW_NUMBER() over (
		PARTITION BY lift_name
		ORDER BY time desc
  	) AS recency
FROM combined_locations;

Part 3 - Getting the latest lift locations:

SELECT
lift_name,
CAST(REPLACE (deck, 'Deck ', '') AS FLOAT) AS deck
FROM found_latest_location
WHERE recency = 1;

Part 4 - Categorizing the risks:

SELECT
lift_name,
malfunction,
CASE
		WHEN malfunction = 'Flooded' OR malfunction = 'Short circuit' THEN 1
		ELSE 0
  	END AS risk_of_electrocution,
  	CASE
		WHEN malfunction = 'Broken drive shaft' OR 'Loss of oxygen' THEN 1
		ELSE 0
  	END AS inoperable,
  	CASE
		WHEN malfunction = 'Lubricant leak' THEN 1
		ELSE 0
  	END AS noisy
FROM lift_malfunctions;

Part 5 - Finding usable lifts:

SELECT
lift_name,
MAX(noisy) AS noisy
FROM categorised_issues
GROUP BY lift_name
HAVING SUM(risk_of_electrocution) < 2
AND inoperable = 0;

Part 6 - Finding the nearest lifts:

SELECT *
FROM usable_lifts
JOIN cleaned_lift_list 
ON cleaned_lift_list.lift_name = usable_lifts.lift_name
WHERE deck < 2;

Full Solution:

WITH combined_locations AS (
SELECT
time,
lift_name,
deck
FROM lift_locations
UNION ALL
SELECT
timestamp,
lift_name,
location
FROM lift_locations_2
), found_latest_location AS (
SELECT
*,
ROW_NUMBER() over (
		PARTITION BY lift_name
		ORDER BY time desc
  	) AS recency
FROM combined_locations
), cleaned_lift_list AS (
SELECT
lift_name,
CAST(REPLACE (deck, 'Deck ', '') AS FLOAT) AS deck
FROM found_latest_location
WHERE recency = 1
), categorised_issues as (
SELECT
lift_name,
malfunction,
CASE
		WHEN malfunction = 'Flooded' OR malfunction = 'Short circuit' THEN 1
		ELSE 0
  	END AS risk_of_electrocution,
  	CASE
		WHEN malfunction = 'Broken drive shaft' OR 'Loss of oxygen' THEN 1
		ELSE 0
  	END AS inoperable,
  	CASE
		WHEN malfunction = 'Lubricant leak' THEN 1
		ELSE 0
  	END AS noisy
FROM lift_malfunctions
), usable_lifts AS (
SELECT
lift_name,
MAX(noisy) AS noisy
FROM categorised_issues
GROUP BY lift_name
HAVING SUM(risk_of_electrocution) < 2
AND inoperable = 0
)
SELECT *
FROM usable_lifts
JOIN cleaned_lift_list 
ON cleaned_lift_list.lift_name = usable_lifts.lift_name
WHERE deck < 2;

Chapter 20: The End

It all comes down to this. Did Helga tell the truth about what she saw? There is only one way to find out. Will you risk your life and everyone else’s lives?

Task:

Make your choice: either look at what's in the table readings or delete everything after 4 June.

This level will behave a little differently; the option you choose will determine which ending your see. If you would like to explore both options, try looking at the data first. Then you can go back and run the deletion query. The deletion ending, however, finishes the game.

Look at the data:

SELECT *
FROM readings
WHERE timestamp >= '1962-06-04';

Delete the data:

DELETE FROM readings
WHERE timestamp >= '1962-06-04';

And with that, the game is complete! You can now see the leaderboard of the players that have finished the game, together with their score. You should be there too! Search for your name and check where you stand.

Ready for Another Game to Learn SQL?

That’s it! You have explored the intricacies of SQL and made sure that your crew reaches the surface safely. If you want to see more games for learning SQL, check out these 5 SQL-based games. And if you’re hungry for even more SQL learning, our SQL Practice track has 1200+ interactive tasks that cover every single corner of SQL! Ready to level up? Dive in now!