1. Learn
    2. /
  2. Courses
    3. /
  3. Reporting in SQL
  • 1

    Exploring the Olympics Dataset

    Free

    Before you can start building out reports to answer specific questions, you should get familiar with the data. In this chapter, you will learn how to use E:R diagrams and data exploration techniques to get a solid understanding of the data to better answer business-related questions.

  • 2

    Creating Reports

    Queries can get large, fast. It's important to take a logical approach when building more complicated queries. In this chapter, you will take a step-by-step approach to plan and build a complex query that requires you to combine tables in multiple ways and create different types of fields.

  • 3

    Cleaning & Validation

    Although it would be nice, data in the real-world is rarely stored in an ideal way. Simply put: data can get messy. In chapter 3, you will learn how to deal with this messy data by fixing data type issues, cleaning messy strings, handling nulls, and removing duplication.

  • 4

    Complex Calculations

    The value of reporting really shows when presenting not-so-obvious insights through complex calculations. In this chapter, you will learn how to build more complicated fields by leveraging window functions and layered calculations. You will gain hands-on experience building two advanced calculations in particular: the percent of a total calculation and the performance index calculation.

Initializing

Exercise

Using date functions on strings

There are several useful functions that act specifically on date or datetime fields. For example:

  • DATE_TRUNC('month', date) truncates each date to the first day of the month.
  • DATE_PART('year', date) outputs the year, as an integer, of each date value.

In general, the arguments for both functions are ('period', field), where period is a date or time interval, such as 'minute', 'day', or 'decade'.

In this exercise, your goal is to test out these date functions on the country_stats table, specifically by outputting the decade of each year using two separate approaches. To run these functions, you will need to use CAST() function on the year field.

Instructions

100 XP
  • Pulling from the country_stats table, select the decade using two methods: DATE_PART() and DATE_TRUNC.
  • Convert the data type of the year field to fix errors.
  • Sum up gdp to get world_gdp.
  • Group and order by year (in descending order).