SQL Technical Test Solutions

sql_solutions.sql

--Please note - I'm using PostgreSQL for these examples
-- Question 1: Write a query that returns the total number of api_requests where the status code was 200
SELECT 
count(status_code) 
FROM api_requests 
WHERE status_code = 200;

-- Question 2: Write a query that gives the count, and average response_ms for every api_endpoint. Sorted by the average
-- I assumed here that ordering we'd be wanting to highlight the highest average
SELECT 
count(api_endpoint), 
AVG(response_ms), 
api_endpoint as average_response_ms 
FROM api_requests 
GROUP BY api_endpoint 
ORDER BY average_response_ms DESC;

-- Question 3: Write a query that gives the total count of api_requests per hour ordered by the hour
SELECT 
count(status_code), 
extract(hour from received) 
from api_requests 
GROUP BY hour;

-- Question 3 - Alternate solution broken down by year, month, day and hour incase the need was to see the data over hours of days 
-- and not just the hour of the day combined
SELECT count(status_code),
extract(year from received) as year,
extract(month from received) as month,
extract(day from received) as day,
extract(hour from received) as hour
from api_requests
GROUP BY year, month, day, hour;

-- Question 4: Write a query that gives the total number api requests for each status code, includes the actual code and the description from the second table
SELECT count(requests.id),
requests.status_code,
codes.description
FROM api_requests as requests
LEFT JOIN status_codes as codes
ON requests.status_code = codes.status_code
GROUP BY requests.status_code, codes.description;