Scribd
Upload
2 views2 pages

SQL Learning Module-1

The document outlines key concepts and techniques for SQL analytics, focusing on time-series analysis, conditionals, joins, and subqueries. It covers basic query structures, data aggregation, handling cumulative data, and the use of CASE statements for classification. Additionally, it emphasizes the importance of proper timestamp handling and analytical thinking workflows in SQL queries.

Uploaded by

David Del Mundo
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views2 pages

SQL Learning Module-1

The document outlines key concepts and techniques for SQL analytics, focusing on time-series analysis, conditionals, joins, and subqueries. It covers basic query structures, data aggregation, handling cumulative data, and the use of CASE statements for classification. Additionally, it emphasizes the importance of proper timestamp handling and analytical thinking workflows in SQL queries.

Uploaded by

David Del Mundo
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL ANALYTICS REVIEWER

Time-Series Analysis, Conditionals, Joins, and Subqueries

Learning Objectives
• Understand data granularity (national vs region vs province)
• Use SELECT, WHERE, ORDER BY correctly
• Apply aggregation (SUM, AVG, GROUP BY)
• Work safely with TIMESTAMP dates
• Convert cumulative data into increases
• Use CASE for classification and conditional aggregation
• Apply JOINs correctly in time-series datasets
• Use subqueries for multi-step analytics

1. Basic Query Structure


SELECT chooses columns:
SELECT region_name, total_confirmed_cases
FROM data_by_region;

WHERE filters rows:


SELECT DATE(date) AS report_date, region_name
FROM data_by_region
WHERE DATE(date) BETWEEN '2021-08-01' AND '2021-10-31';

ORDER BY sorts results:


ORDER BY report_date, region_name;

2. Aggregation
SELECT region_name,
DATE_TRUNC(DATE(date), MONTH) AS month,
SUM(new_total_confirmed_cases) AS monthly_increase
FROM data_by_region
GROUP BY region_name, month;

3. Cumulative vs Incremental Data


Cumulative fields cannot be summed directly.
To compute period increase use:
MAX(value) − MIN(value)
SELECT province_name,
DATE_TRUNC(DATE(date), MONTH) AS month,
MAX(confirmed_cases) - MIN(confirmed_cases) AS monthly_increase
FROM data_by_province
GROUP BY province_name, month;

4. Working With TIMESTAMP Dates


Always convert timestamps before filtering or joining:
DATE(date)
5. JOINs in Time-Series Data
Always join on entity + date:
SELECT R.region_name,
DATE([Link]),
R.total_confirmed_cases,
C.total_confirmed_cases
FROM data_by_region R
JOIN data_by_country C
ON DATE([Link]) = DATE([Link]);

6. CASE (Conditionals)
CASE
WHEN new_current_confirmed_cases < 0 THEN 'Negative'
WHEN new_current_confirmed_cases BETWEEN 0 AND 500 THEN 'Low'
WHEN new_current_confirmed_cases BETWEEN 501 AND 1000 THEN 'Medium'
ELSE 'High'
END AS level

7. Conditional Aggregation
SUM(CASE
WHEN DATE(date) BETWEEN '2020-10-01' AND '2020-12-31'
THEN new_total_confirmed_cases
ELSE 0
END)

8. Subqueries
Use subqueries when an intermediate calculation is required.

Example: Get latest available date


WHERE DATE(date) = (
SELECT MAX(DATE(date))
FROM data_by_country
)

Example: Aggregate first, then compare


FROM (
SELECT DATE(date), SUM(new_current_confirmed_cases)
FROM data_by_region
GROUP BY DATE(date)
) regional_totals

9. Analytical Thinking Workflow


1. Identify the data grain
2. Identify whether the metric is cumulative or incremental
3. Choose CASE, JOIN, or Subquery as needed
4. Aggregate at the correct level (day, month, region)

End of Reviewer

You might also like