Problem Description

We need to design a data structure to store the results of the following SQL query:

CREATE TABLE new_customers_each_month AS
SELECT EXTRACT(YEAR FROM order_date) AS year,
       EXTRACT(MONTH FROM order_date) AS month,
       COUNT(DISTINCT customer_id) AS new_customers
FROM sales
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM sales
    WHERE EXTRACT(YEAR FROM order_date) > (SELECT MIN(EXTRACT(YEAR FROM order_date)) FROM sales)
)
GROUP BY year, month;

Data Structure Recommendation

To efficiently store and manage the results of the given SQL query, we can use a table-like data structure called a hash table or dictionary. This data structure allows us to store key-value pairs, where the key represents a combination of year and month, and the value represents the count of new customers for that month.

Code Template

Below is a sample code template for implementing the recommended data structure:

# Create an empty hash table to store the new customers count for each month
new_customers_each_month = {}

# Iterate through the result of the SQL query
for row in query_result:
    year = row['year']
    month = row['month']
    new_customers = row['new_customers']
    
    # Create a key by combining the year and month
    key = (year, month)
    
    # Store the new customers count in the hash table
    new_customers_each_month[key] = new_customers

The above code uses a dictionary in Python to represent the hash table. The query_result is an iterable (e.g., list, generator) that contains the result of the SQL query, where each row is a dictionary-like object with 'year', 'month', and 'new_customers' as the keys.

Key Points

• The recommended data structure is a hash table or dictionary.
• The data structure will store key-value pairs, where the key represents a combination of year and month, and the value represents the count of new customers for that month.
• The code template uses a dictionary in Python as an example implementation.
• The code template assumes the existence of a query_result iterable that contains the result of the SQL query, where each row is a dictionary-like object with 'year', 'month', and 'new_customers' as the keys.