Customer Session: Writing Snowflake Queries That Actually Return Results

Slides

Snowflake Query Examples

Example #1

Improve the following query:

select max(o_totalprice) from 
SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.orders
where date_part('epoch_second', o_orderdate)<=  date_part('epoch_second', '1992-03-04'::timestamp);

Hint 1

Try and find the clustering key by running:

select SYSTEM$CLUSTERING_INFORMATION( 'SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.orders' );

Hint 2

Read the Explain:

EXPLAIN USING JSON (  
  select max(o_totalprice) from  
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.orders  
  where date_part('epoch_second', o_orderdate)\<=  date_part('epoch_second', '1992-03-04'::timestamp)  
);

Example #2

Improve the following query:

SELECT C.customer_key,  
       C.customer_name,  
       C.customer_nation_key,  
       O.O_ORDERKEY AS NUMBER_OF_ORDERS,  
       O.O_TOTALPRICE AS TOTAL  
  FROM (  
  SELECT C_CUSTKEY AS customer_key,  
         C_NAME as customer_name,  
         C_NATIONKEY as customer_nation_key  
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.CUSTOMER  
  WHERE customer_nation_key = 23  
  ) C  
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF10.ORDERS O ON C.customer_key=O.O_CUSTKEY;

Example #3

Improve the following query:

SELECT C.C_NATIONKEY,  
       COUNT(O.O_ORDERKEY) AS NUMBER,  
       SUM (O.O_TOTALPRICE) AS TOTAL  
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.CUSTOMER C  
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.NATION N ON C.C_NATIONKEY=N.N_NATIONKEY  
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.ORDERS O ON C.C_CUSTKEY=O.O_CUSTKEY  
GROUP BY C.C_NATIONKEY  
ORDER BY NUMBER DESC;

Example #4

Improve the following query:

select distinct  
      l_returnflag,  
      l_linestatus,  
      concat(l_returnflag, '_', l_linestatus) as formatted_flag,  
      count(\*) as count_order  
from  
    SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.lineitem  
where  
    l_shipdate \<= dateadd(day, -90, to_date('1998-12-01'))  
group by  
    l_returnflag,  
    formatted_flag,  
    l_linestatus  
order by  
    l_returnflag,  
    l_linestatus;

Example #5

Improve the following query:

SELECT CASE  
            WHEN O_CUSTKEY >= 10000000 THEN 10000000  
            ELSE O_CUSTKEY  
       END AS  RELABLED_CUSTOMER_KEY,  
       O_ORDERPRIORITY as PRIORITY,  
       COUNT(O_ORDERKEY) AS NUMBER,  
       SUM (O_TOTALPRICE) AS TOTAL  
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.ORDERS  
GROUP BY RELABLED_CUSTOMER_KEY, O_ORDERPRIORITY;

ב