Menu
SQL Certification

SQL Certification Queries

This section contains solutions to SQL questions from certification programs on platforms like HackerRank, LeetCode, and more, demonstrating advanced SQL proficiency.

HackerRank HackerRank

Solutions to problems from HackerRank's SQL Certification test, covering advanced topics like complex joins, subqueries, and window functions.

Weather Analysis: There is a table with daily weather data over the last 6 months of 2020, including the maximum, minimum, and average temperatures. Write a query that gives month, monthly maximum, monthly minimum, monthly average temperatures for the six months.
Temperature_Records Table: Record_Date VARCHAR(10), Data_Type VARCHAR(3), Data_Value INT
Advanced 
SET NULL "NULL";
SET FEEDBACK OFF;
SET ECHO OFF;
SET HEADING OFF;
SET WRAP OFF;
SET LINESIZE 10000;
SET TAB OFF;
SET PAGES 0;
SET DEFINE OFF;
SET SERVEROUTPUT ON;

SELECT 
    TO_NUMBER(TO_CHAR(TO_DATE(record_date, 'YYYY-MM-DD'), 'MM')) AS recMonth, 
    MAX(data_value) AS max, 
    MIN(data_value) AS min,
    round(AVG(CASE WHEN data_type = 'avg' then data_value END)) AS avg
FROM 
    temperature_records
WHERE 
    TO_NUMBER(TO_CHAR(TO_DATE(record_date, 'YYYY-MM-DD'), 'MM')) BETWEEN 7 AND 12
GROUP BY 
    TO_NUMBER(TO_CHAR(TO_DATE(record_date, 'YYYY-MM-DD'), 'MM'))
ORDER BY 
    TO_NUMBER(TO_CHAR(TO_DATE(record_date, 'YYYY-MM-DD'), 'MM'));

exit;