Data Deduplication with DISTINCT

Hard
26
67.2% Acceptance

Welcome to this SQL lab! Here, you'll test and hone your SQL skills using the Chinook dataset and the SQLite database system.

Dataset Overview:

The Chinook dataset emulates a digital media store, containing tables related to albums, artists, tracks, invoices, and more.

Here are some key tables you'll be working with:

  • "Artist": Contains artist details.
  • "Album": Holds information about each album, linked to artists.
  • "Track": Details about individual tracks, their genres, media types, and the albums they belong to.
  • "Customer": Data about customers.
  • "Invoice": Billing information for purchases made by customers.

Environment:

  • This lab utilizes SQLite. Ensure you're familiar with its syntax and functionalities.
  • Make sure to stick to the exact names for tables and columns as defined in the Chinook database schema.
  • Always use double quotes for column and table names for clarity.

Challenges:

Your main task is to complete a set of SQL challenges. Each challenge will test a specific aspect of SQL, and you're expected to write SQL queries to fetch, analyze, or manipulate data from the Chinook dataset.

Objective:
Retrieve the most recent invoice date for every customer in the Chinook database.

Tables Used:

  • Customer
  • Invoice

Instructions:

  1. Join the Customer table with the Invoice table using the appropriate key.
  2. For each customer, determine and select the most recent invoice date.
  3. Your result should display the following columns:
    • CustomerId
    • MaxInvoiceDate (this should be an alias for the latest invoice date for clarity)

Output Specifications:

  • Columns:

    1. CustomerId
    2. MaxInvoiceDate

  • Order:

    • The results should be ordered by CustomerId in ascending order.

Hint:
Consider using aggregation functions and grouping to achieve the desired result. Ensure all columns are named accurately as described to pass the evaluation.

Notes:

  • Carefully read each challenge's description. Pay close attention to the expected output and the order of results.
  • Always ensure that your SQL queries match the specified requirements to pass the evaluation.