Solución caso SQL Murder Mystery

Solución caso SQL Murder Mystery

https://mystery.knightlab.com/#experienced

Reporte escena del crimen

SELECT *

FROM crime_scene_report

WHERE

        DATE = '20180115'

        AND type = 'murder'

        AND city LIKE '%SQL%'

Testimonio de personas que vieron el asesinato: testigos afirman que el asesinato fue el 09-01

SELECT

        id,

        name,

        license_id,

        address_number,

        address_street_name,

        ssn,

        transcript

FROM person as p

LEFT JOIN interview as i ON p.id = i.person_id

WHERE

        address_street_name = 'Northwestern Dr'

        OR (address_street_name = 'Franklin Ave' AND name LIKE '%Annabel%')

ORDER BY 5, 4 DESC

LIMIT 2

Investigar personas miembros gold del gym, con número de membresía 48Z,también investigar placa del auto que incluye H42W

Ver todas las personas que entrenaron el 09-01

Query para encontrar el nombre del asesino

SELECT

        membership_id,        

        check_in_date,

        m.person_id,

        m.name,

        license_id,

        plate_number,

        transcript

FROM get_fit_now_check_in as c

LEFT JOIN get_fit_now_member as m ON c.membership_id = m.id

LEFT JOIN person as p ON m.person_id = p.id

LEFT JOIN drivers_license as l ON p.license_id = l.id

LEFT JOIN interview as i ON p.id = i.person_id

WHERE

        check_in_date = '20180109'

        AND membership_id LIKE '48Z%'  

        AND plate_number LIKE '%H42W%'

Query para buscar al verdadero la mente detrás del asesinato

SELECT

        person_id,

        name,

        height,

        gender,

        hair_color,

        event_name,

        date,

        car_make,

        car_model

FROM facebook_event_checkin as f

LEFT JOIN person as p ON f.person_id = p.id

LEFT JOIN drivers_license as l ON p.license_id = l.id

WHERE

        DATE BETWEEN '20171201' AND '20171231'

        AND event_name = 'SQL Symphony Concert'

        AND lower(car_make) LIKE '%tesla%'

...