Practice-Astronauts EVAs

-- Start writing your SQL query here 

SELECT * FROM evas LIMIT 10;

SELECT COUNT(DISTINCT(purpose)) AS unique_purposes FROM evas;

SELECT purpose,
	CASE WHEN purpose LIKE '%install%' OR purpose LIKE '%construct%' THEN 1 ELSE 0 END AS installation,
    CASE WHEN purpose LIKE '%photo%' THEN 1 ELSE 0 END AS photography,
    CASE WHEN purpose LIKE '%collect%' THEN 1 ELSE 0 END AS collection,
    CASE WHEN purpose LIKE '%recover%' OR purpose LIKE '%retrieve%' THEN 1 ELSE 0 END AS retrieval,
    CASE WHEN purpose LIKE '%Jjettison%' THEN 1 ELSE 0 END AS jettison,
    CASE WHEN purpose LIKE '%repair%' OR purpose LIKE '%fix%' OR purpose LIKE '%replace%' OR purpose LIKE '%service%' THEN 1 ELSE 0 END AS repair
FROM evas;
    

-- Using ILIKE (case-insensitive) instead of LIKE (case-sensitive)


WITH purposes AS(
    SELECT purpose,
        CASE WHEN purpose ILIKE '%install%' OR purpose ILIKE '%construct%' OR purpose ILIKE '%assembl%' THEN 1 ELSE 0 END AS installation,
        CASE WHEN purpose ILIKE '%photo%' THEN 1 ELSE 0 END AS photography,
        CASE WHEN purpose ILIKE '%collect%' THEN 1 ELSE 0 END AS collection,
        CASE WHEN purpose ILIKE '%recover%' OR purpose ILIKE '%retrieve%' THEN 1 ELSE 0 END AS retrieval,
        CASE WHEN purpose ILIKE '%jettison%' THEN 1 ELSE 0 END AS jettison,
        CASE WHEN purpose ILIKE '%repair%' OR purpose ILIKE '%fix%' OR purpose ILIKE '%replace%' OR purpose ILIKE '%servic%' THEN 1 ELSE 0 END AS repair
    FROM evas
)
    
    
SELECT 'installation' AS type, SUM(installation) AS total
FROM purposes
UNION
SELECT 'photography' AS type, SUM(photography) AS total
FROM purposes
UNION
SELECT 'collection' AS type, SUM(collection) AS total
FROM purposes
UNION
SELECT 'retrieval' AS type, SUM(retrieval) AS total
FROM purposes
UNION
SELECT 'jettison' AS type, SUM(jettison) AS total
FROM purposes
UNION
SELECT 'repair' AS type, SUM(repair) AS total
FROM purposes
ORDER BY total;

--Filter purpose for Geological samples collection

SELECT purpose 
FROM evas
WHERE purpose ILIKE '%rock%' OR purpose ILIKE '%geologic%';

--Using Regex symbols to extract weight from EVA description

SELECT
	purpose,
    SUBSTRING(purpose, '\d+\.?\d*') AS weight
FROM evas
WHERE purpose ILIKE '%rock%' OR purpose ILIKE '%geologic%';

--Prepare CTE and calculate total_rock_collections weight

WITH rock_collections AS (
	SELECT
    purpose,
    SUBSTRING(purpose, '\d+\.?\d*')::NUMERIC AS weight
    FROM evas
    WHERE purpose ILIKE '%rock%' OR purpose ILIKE '%geologic%'
)


SELECT SUM(weight) AS total_rock_collections 
FROM rock_collections;

--Split multiple Astronaut names from crew

SELECT 
	crew,
    SPLIT_PART(crew, ',', 1) AS first_astronaut,
	SPLIT_PART(crew, ',', 2) AS second_astronaut,
	SPLIT_PART(crew, ',', 3) AS third_astronaut
FROM evas;

-- Prepare & Use CTEs (astronauts_list and astronauts_duration)

WITH astronauts_list AS (
	SELECT 
	crew, duration,
    SPLIT_PART(crew, ',', 1) AS first_astronaut,
	SPLIT_PART(crew, ',', 2) AS second_astronaut,
	SPLIT_PART(crew, ',', 3) AS third_astronaut
	FROM evas
),

astronauts_duration AS (
	SELECT first_astronaut AS astronaut, duration
    FROM astronauts_list
    WHERE first_astronaut != ''
    UNION ALL
    SELECT second_astronaut AS astronaut, duration
    FROM astronauts_list
    WHERE second_astronaut != ''
    UNION ALL
    SELECT third_astronaut AS astronaut, duration
    FROM astronauts_list
    WHERE third_astronaut != ''
)



SELECT astronaut, SUM(duration) AS total_duration
FROM astronauts_duration
GROUP BY astronaut
ORDER BY total_duration DESC
LIMIT 15;