PostgreSQL Summary Stats and Window Functions
  • AI Chat
  • Code
  • Report
    • Beta
    Spinner

    PostgreSQL Summary Stats and Window Functions

    👋  Welcome to your new workspace! Here, you can experiment with the Olympics data you used in PostgreSQL Summary Stats and Window Functions and practice your newly learned skills with some challenges. You can find out more about DataCamp Workspace here.

    This workspace takes about 30 minutes to complete, but you are free to experiment as long as you like!

    1. Get Started

    Below is a SQL cell. It is used to execute SQL queries. There is already a pre-written query to get you started that assigns a quartile to each country based on their medal count.

    You can click the "Browse tables" button in the upper righthand corner of the cell below to view the available tables.

    Note: The databases from different PostgreSQL courses are available, which you can preview using the dropdown menu to the left. To access the medals data, you will need to add this schema name to access the summer_medals table in your query (i.e., medals.summer_medals).

    Unknown integration
    DataFrameavailable as
    movie_info
    variable
    WITH country_medals AS (
  SELECT country, COUNT(*) AS medal_count
  FROM medals.summer_medals
  GROUP BY country
)
  
SELECT
  country,
  medal_count,
  -- Split athletes into thirds by their earned medals
  NTILE(4) OVER (ORDER BY medal_count DESC) AS quartile
FROM country_medals
ORDER BY medal_count DESC, country;
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    2. Your Turn

    Now it's your turn to try out some challenge queries using the cells below. To start, update the sample query below to:

    • Select the athlete, event, and year from the summer_medals table.
    • Add another column, previous_winner, which contains the previous winner of the same event.
    • Filter your results for gold medalists.

    🏃  To execute a query, click inside the cell to select it and click "Run" or the ► icon. You can also use Shift-Enter to run a selected cell and automatically navigate to the next cell.

    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT *
FROM medals.summer_medals
LIMIT 5
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    3. Keep going!

    Continue to flex your SQL skills and update the sample query below to:

    • Return the year, total number of medalists per year, and running total number of medalists in the history of the Summer Olympics.
    • Order your results by year in ascending order.
    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT *
FROM medals.summer_medals
LIMIT 5
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    4. Final Challenge!

    Great work! Now let's throw one final challenge your way. Update the sample query below to:

    • Return the country, year, and the number of gold medals earned.
    • Limit your results to the years 2004, 2008, and 2012.
    • Each country should have a subtotal of all gold medals earned across the three years.
    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT *
FROM medals.summer_medals
LIMIT 5
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    5. Next Steps

    Great work! Feel free to continue to experiment with these tables by creating a new SQL cell below, or if you're interested in more, try the following options: