---
title: "Customer Session: Writing Snowflake Queries That Actually Return Results"
slug: "customer-session-writing-snowflake-queries-that-actually-return-results"
updated: 2023-05-31T11:32:48Z
published: 2023-05-31T11:32:48Z
canonical: "docs.hunters.ai/customer-session-writing-snowflake-queries-that-actually-return-results"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.hunters.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Customer Session: Writing Snowflake Queries That Actually Return Results

[Embedded content](https://www.youtube.com/embed/YDUMz-rSfAw)

## Slides

[Embedded object](https://cdn.document360.io/5f63f0e0-3c70-4d00-b623-82493be0bde5/Images/Documentation/Webinar%20-%20Writing%20Snowflake%20queries%20that%20actually%20return%20results.pdf)

## 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;
```

ב

## Attachments

- [Webinar - Writing Snowflake queries that actually return results.pdf](https://cdn.document360.io/5f63f0e0-3c70-4d00-b623-82493be0bde5/Images/Documentation/Webinar - Writing Snowflake queries that actually return results.pdf)