The Stolen Encore

BRISTOL, ENGLAND · DIFFICULTY: MISDEMEANOR

THE BRIEFING

The winner of Friday Night Live would open the city's biggest music festival. At 9:50 p.m. Velvet Static was losing. Ten minutes later they had won. The station put it down to an enthusiastic fan base until an engineer found that the one-vote-per-phone rule had been switched off. Somebody bought the band a victory. Start with the broadcast record, then look at the winning act's accepted votes in the final ten minutes. Find the telephone line behind the largest number of them. It belongs to a building that rents rooms by the hour. Find who was using that room when voting closed, and what they ordered.

CLUES ON THE TABLE

01.The broadcast under suspicion is Friday Night Live on 18 October 1879.
02.Only accepted votes counted. Look at the winning act's votes in the final ten minutes, up to and including the closing time.
03.The operation ran on a single telephone line. That line sent more accepted votes for the winner in that window than any other.
04.The building rents the same rooms to different customers through the day. Find the booking that started before voting closed and ended after it.
05.Bookings do not name individual guests, but extras are billed to a person. Find the outgoing-call automation order on that booking.

/ THE EVIDENCE

Database Schema

These are the tables you can query. The lines are the keys that join them. Hover a table to see what it connects to.

broadcasts6 COL

broadcast_idINT
show_nameVARCHAR(150)
broadcast_dateDATE
voting_opened_atTIMESTAMP
voting_closed_atTIMESTAMP
winning_actVARCHAR(150)

phone_votes6 COL

vote_idINT
broadcast_idINT
→ broadcasts.broadcast_id
source_numberVARCHAR(30)
→ phone_lines.phone_number
act_nameVARCHAR(150)
received_atTIMESTAMP
vote_statusVARCHAR(30)
accepted, rejected or cancelled

phone_lines4 COL

phone_numberVARCHAR(30)
building_nameVARCHAR(150)
street_addressVARCHAR(200)
room_numberVARCHAR(20)
NULL if not a rented room

room_bookings6 COL

booking_idINT
booking_referenceVARCHAR(30)
phone_numberVARCHAR(30)
→ phone_lines.phone_number
company_nameVARCHAR(150)
starts_atTIMESTAMP
ends_atTIMESTAMP

service_orders6 COL

order_idINT
booking_referenceVARCHAR(30)
→ room_bookings.booking_reference
service_descriptionVARCHAR(200)
ordered_atTIMESTAMP
amount_paidDECIMAL(10,2)
paid_byINT
→ suspects.suspect_id

suspects4 COL

suspect_idINT
nameVARCHAR(100)
occupationVARCHAR(100)
connection_to_competitionVARCHAR(200)

JOINS TO ANOTHER TABLEJOINED FROM ANOTHER TABLE

/ THE QUERY TERMINAL

Run your queries

Use SQL to explore the data. Each table holds part of the answer.

query.sql · 0 queries

or Ctrl+Enter

/ NOTES

Your notepad

Write down anything useful as you go.

NOTES

/ THE ACCUSATION

Name your suspect

Type the suspect's full name and submit.

NEXT CASE

The Rhyme at Alderwick Hall →

"Sir Rowan collapsed in his locked study. No one said they had gone in. The only witness was a poem."