Using Vanna.AI’s demo sales data effectively

On Vanna.AI, we just introduced (as of May 17, 2023) a new demo dataset — the TPC-H dataset that resembles a business, that comes default with each installation of Snowflake.

What’s in the sales data (tables & columns)

Let’s take a quick peak at what’s inside this dataset.

Here’s the structure, nicely provided by Snowflake. Customers create orders, which have line items, which are parts that are supplied by suppliers.

Asking some questions

Ok, let’s ask a few questions. First, let’s try a basic question to find the countries with the most customers.

what top 10 countries have the most customers? show the name of the country and number of customers.

Here’s the SQL generated, joining 3 tables together —

SELECT n.n_name AS country_name, COUNT(c.c_custkey) AS number_of_customers
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION n
JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER c ON n.n_nationkey = c.c_nationkey
GROUP BY country_name
ORDER BY number_of_customers DESC
LIMIT 10

And here’s the table —

Next, let’s try a much more complex question —

What are the top 10 customers that have the highest value of returned brass parts where each part is more than $10 in Germany?

This SQL is extremely complex, touching five tables and using several nested queries.

SELECT 
    c.c_name AS customer_name, 
    SUM(l.l_extendedprice * l.l_discount) AS returned_parts_gross_value
FROM 
    SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM l 
    JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS o 
        ON l.l_orderkey = o.o_orderkey
    JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER c 
        ON o.o_custkey = c.c_custkey
    JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.SUPPLIER s 
        ON l.l_suppkey = s.s_suppkey
    JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION n 
        ON s.s_nationkey = n.n_nationkey
    WHERE 
        l.l_returnflag = 'R' 
        AND s.s_nationkey = (
            SELECT 
                n.n_nationkey 
            FROM 
                SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION n 
            WHERE 
                n.n_name = 'GERMANY'
        )
        AND l.l_extendedprice * (1 - l.l_discount) > 10 
        AND l.l_partkey IN (
            SELECT 
                p.p_partkey 
            FROM 
                SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.PART p 
            WHERE 
                p.p_type LIKE '%BRASS%'
        )
GROUP BY 
    customer_name
ORDER BY 
    returned_parts_gross_value DESC
LIMIT 
    10

But the AI got it right and here are the results —