Week 9 - SQL for Analytics

Introduction to SQL for Analytics

2. Joins, CTEs, and Aggregations

3. Data Validation Queries

4. Data Modeling Concepts

5. Building Views in Azure PostgreSQL

6. Gotchas & Pitfalls

9. Assignment

Assignment

Content coming soon...

Suggested Project Scope

Using the NYC taxi dataset loaded into your Azure PostgreSQL database:

Write validation queries for the raw trips data: check for duplicates, NULL location IDs, negative fare amounts, and row count consistency
Design a star schema: trips as the fact table, zones as the dimension table
Implement vwfacttrips and vwdimzones as SQL views in Azure PostgreSQL
Document the grain, keys, and measures for each view

Suggested Verification Questions

After building your views, answer these by querying them:

How many rows does vwfacttrips contain?
Which pickup zone had the highest total fare revenue?
What is the total number of trips per borough?
Which month had the most trips?

These questions have known answers (everyone uses the same dataset), so teachers can verify correctness.

Suggested Deliverables

SQL scripts for validation queries, view definitions, and analytical queries
Validation query results (screenshots or output)
A short data dictionary: grain, keys, and measures for each view
Answers to the verification questions above

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 *https://hackyourfuture.net/*

CC BY-NC-SA 4.0 Icons

Built with ❤️ by the HackYourFuture community · Thank you, contributors

Found a mistake or have a suggestion? Let us know in the feedback form.