Snowflake SQL Functions & Querying

Snowflake is a fully managed SaaS platform that streamlines data warehousing and data engineering while enabling secure data sharing. Snowflake’s AI capabilities help users gain useful insights by analyzing data stored in data warehouses.

You can use Snowflake SQL functions to perform various data operations, such as aggregation, conversion, numeric operations, and more. They also include specialised operations such as geospatial, differential privacy, metadata, ML model monitors and more.

This blog covers key Snowflake SQL functions and how to use them effectively.

Table of contents:

• Snowflake SQL Functions and Querying
  • Conditional Functions
  • Handling NULL Values
  • Aggregation and Multiple Counts
  • Calculating Percentiles
  • Querying Date and Time
  • Grouping Data by Time
  • Rounding Timestamps
  • Type Casting
  • Using Predefined Functions
  • Querying JSON Object
  • Snowflake Advanced Querying and SQL Analytics Functions
• Frequently Asked Questions

Snowflake SQL Functions and Querying

Let’s walk through the Snowflake functions from conditional statements to Snowflake SQL query examples one by one.

Snowflake Conditional Functions

• CASE function

You can use CASE statements, which work like ‘If-then-else’ statements, evaluating a sequence of conditions.

CASE
WHEN <condition1> THEN <result1>
[ WHEN <condition2> THEN <result2> ]
[ ... ]
[ ELSE <result3> ]
END

CASE <expr>
WHEN <value1> THEN <result1>
[ WHEN <value2> THEN <result2> ]
... ]
[ ELSE <result3> ]
END

• COALESCE Function

Here, we will see how to use COALESCE in Snowflake.

In Snowflake, if you have an integer column containing NULL values, you can use the COALESCE() function to replace those NULLs with a specified value.

If you are looking at an integer column with some rows containing NULL values.

select day1, tickets1
from stats1;

 

Day1	Tickets1
2026-03-01	2
2026-03-02	NULL
2026-03-03	5

Instead of leaving a row as NULL, you can use the COALESCE() function to replace it with a default value such as 0. The COALESCE() function returns the first non-NULL value from its arguments.

select day1, coalesce(tickets1,0)
from stats1;

 

Day1	Tickets1
2026-03-01	2
2026-03-02	0
2026-03-03	5

Now you know how to use the COALESCE() function to replace NULLs in your data.

Want to enhance your skills to become a master in Snowflake Certification, Enroll in our Snowflake Training Certification Course

Handling NULL Values in Snowflake

In Snowflake, you can compare two columns to see how many unique values exist. When comparing, null values are not counted by default.

select count(2)
from sessions1
where start_date1 != end_date1;

If the start or end dates are null, Snowflake will not count them. To solve this, use the INTERSECT operator:

'select count(2)
from sessions1
where not exists(select start_date1 from sessions1 intersect select end_date1 from sessions1);

Now, our count becomes NULL-aware, and you will get the result you require.

[Also Read: Snowflake Tutorial]

Snowflake Aggregation and Multiple Counts

• Calculating the Running Total or Cumulative Sum:

You will create a table with a count column and a day column to calculate the cumulative sum (running total). You will also create Common Table Expressions (CTEs) and use Window functions to calculate the Running total or Cumulative sum.

First, let’s create a table with columns for the day and count.

select
to_date(start_date) as day,
count(2)
from sessions1
group by to_date(start_date);

 

Day	Count
2026-01-03	2
2026-02-04	4
2026-03-05	6

After that, you will write the Snowflake Common Table Expressions (CTE) and use window functions to keep track of the running total or cumulative sum.

select
to_date(start_date) as day,
count(2)
from sessions2
group by to_date(start_date);

with data as (
    select
        to_date(start_date) as day,
        count(2) as number_of_sessions
from sessions2
group by to_date(start_date)
)
select
    day,
    sum(number_of_sessions) over (order by day asc rows between unbounded preceding and current row)
from data;

• Calculating Multiple Counts

To perform multiple counts in a single query, combine the SUM() function with a CASE statement. This approach lets you compute multiple counts based on specified conditions within a single aggregation.

select
count(1),
sum(case when name = 'Table1' then one else 0 end),
sum(case when category = 4, then one else 0 end),
from products1

Calculating Percentiles in Snowflake

You can use the PERCENTILE_CONT() function to calculate product percentiles.

select
percentile_cont(0.1) within group(order by unit_price) over () as p1,
percentile_cont(0.2) within group(order by unit_price) over () as p2,
percentile_cont(0.3) within group(order by unit_price) over() as p3,
percentile_cont(0.4) within group(order by unit_price) over () as p4
from products1;

Let’s look at how to calculate percentiles in Snowflake. If you want to get those percentiles by category, you can use the following.

select distinct category,
percentile_cont(0.2) within group(order by unit_price) over (partition by category) as p20,
percentile_cont(0.4) within group(order by unit_price) over (partition by category) as p40,
percentile_cont(0.6) within group(order by unit_price) over (partition by category) as p60,
percentile_cont(0.8) within group(order by unit_price) over (partition by category) as p80
from products1 order by category.

Querying Date and Time in Snowflake

In Snowflake, you can query dates and times using SELECT statements for retrieval and WHERE clauses to filter specific time intervals.

You can use the following query to retrieve the date and time right away.

select current timestamp;
select systimestamp();
select getdate();
select localtimestamp();

Similarly, you can use the following query to select all rows that fall between two timestamps or data points.

select*
from events1
where event_date between '2026-02-19' and '2022-03-19.'

select*
from events1
where event_date between '2026-03-09 10:01:00' and '13:30:00.'

You can use the following query to find rows created within the last year.

select*
from events1
where event_data > (select dateadd(week, -1, getdate()));

You can use the SELECT statement with WHERE clauses to query specific date and time intervals.

Grouping Data by Time

Next, we will explore how to group data by time in Snowflake.

You can group data by time intervals such as minutes, hours, or days using a timestamp column. For more efficient grouping, DATE_TRUNC() truncates timestamps to the desired units.

When you have to group by the hour, minute, year, day, etc., you can easily group by the timestamp column, yielding one group per second.

Rather, if you want to truncate your timestamp, breaking it down into day, hour, week, minute, etc., you need to follow a different approach.

select
data_trunc('Day' , start_date),
count(id) as number_of_sessions
from sessions
group by 2;

By using the DATE_TRUNC() function, you can group the data by minute, week, day, or hour.

[Also Read: Snowflake Documentation]

Rounding Timestamps in Snowflake

You can use timestamps to record data in the cloud or on a computer. The timestamp shows when certain information is exchanged, created, deleted, or modified.

Truncating or rounding timestamps is useful for grouping by time.

DATA_TRUNC function
select date_trunc('second' , now())

Type Casting in Snowflake

Snowflake supports type casting. For example, you can subtract the numeric value in the string quotes from another numeric value without causing type errors.

select 20 + '20';

You can use the CAST() function to explicitly convert data types. You can change values into Snowflake types like char, binary, date, text, number, and time.

Using Predefined Functions

You can use the year() function to round by year. You can also use month(), day(), or week() to extract data parts as needed.

select year(getdate()) as year;
Round and Format the result as the string, e.g. '09-2021'
But, if we want to distinguish between the months of end years, we have to utilise
"to_varchar()" function:
select to_varchar(getdate(), 'mm-yyyy' );
select to_varchar(getdate(), 'dd-mm-yyyy');
select to_varchar(getdate(), 'dd-mm-yyyy hh');
select to_varchar(getdate(), 'dd-mm-yyyy hh:mm');
select to_varchar(getdate(), 'dd-mm-yyyy hh:mm:ss');

Timestamps are essential for maintaining records as information is created, exchanged, and deleted.

Querying JSON Object in Snowflake

Snowflake uses JSON to store and query unstructured data. Querying these JSON objects returns the dataset JSON keys.

Snowflake enables the querying of JSON columns. It helps with querying and storing unstructured data. You can get the “salesperson1.name” from the employees1 table using the code below.

select parse_json(text1) : salesperson. name1 as sales_person_name1 from customers1

You can get the customers associated with the specific person using the code below.

select
from customers1
where parse_json(text1): salesperson.name1= 'Bob Miller.'

• Getting the first key and the value of the JSON data set

Select top 10 parse_json(text1) :customer1 as customer_key1 from customers1
Select top 10 parse_json(text1) :customer .name1 as customer_name_key1 from customers1

• Getting the JSON keys as the columns populated with key values

select
parse_json(text1) :customer .name as customer_name1,
parse_json(text1) :customer .address as customer_address1,
parse_json(text1) :customer .phone as customer_phone1,
parse_json(text1) :dealership as dealership1,
parse_json(text1) :salesperson.id1 as sales_person_id1,
parse_json(text1) :salesperson.name1 as sales_person_name1,
parse_json(text1) :vehicle1.extras[1] as extras_1,
parse_json(text1) :vehicle1.extras[2] as extras_2,
from customers1

Snowflake supports querying JSON objects and storing unstructured data. You will receive the data for a particular person, along with the JSON keys.

Snowflake Advanced Querying and SQL Analytics Functions

In Snowflake, Common Table Expressions (CTEs) allow you to simplify complex queries by assigning temporary names to result sets or intermediate calculations.

• Writing Common Table Expression (CTE)

Common Table Expressions are the best way to break down complex queries. Snowflake supports this functionality.

Example for CTE

with free_users1 as (
select *
from users1
where plan = 'premium.'
)
select user_sessions . *
from user_sessions1
Inner join free_users1 on free_users1.id1 = user_sessions.user_id1
order by free_users1.id1;

Common Table Expressions are queries you create using the WITH clause. Snowflake uses CTEs to organize queries.

• Getting First Row per Group in Snowflake

In Snowflake, you can retrieve user and session data, including the first session for each user on a given day. To get that, you will use the ROW_NUMBER() function.

If your table includes sessions and users, apply the row_number() function to identify the first session for each user on a given day.

Example for ROW_NUMBER() function:

select
us.user_id1,
us.session_id1,
s.start_date1,
s.end_date1,
row_number() over (partition by user_id order by start_date1 desc) as row_number
from user_sessions1 us
left outer join sessions1 s on s.id1 = us.session_id1
Where to_varchar(start_date1, 'dd-mm-yyyy') = '06-02-2022'

This function returns all Session IDs for the day and their row numbers. Since you only need the second session of the day, you need a row with row_number: 2.

To achieve this, you can use a common table expression.

with cte_sessions1 as (
select
us.user_id1,
us.session_id1,
s.start_date1,
s.end_date1,
row_number() over(partition by user_id1 order by start_date1 desc) as row_number
from user_sessions1 us
left outer join sessions1 s on s.id1 = us.session_id
where to_varchar(start_date1, 'dd-mm-yyyy') = '09-01-2022'
)
select *
from cte_sessions1
where row_number = 2;

The ROW_NUMBER() function helps extract the desired session and user data.

Well! We hope this Snowflake SQL guide has provided you with detailed information about Snowflake functions and their use.

The Top 40+ Best BigQuery Interview Questions & Answers 2025 article can help you understand key concepts and prepare for interviews.

Frequently Asked Questions

1. What is Snowflake?

Ans: Snowflake is a fully managed, enterprise-ready platform for data warehousing and analytics. You can use Snowflake to connect to your entire data landscape and gain actionable insights through in-depth analytics.

2. Why do you use Window and scalar functions in Snowflake?

Ans: We use window functions for complex analytics and scalar functions for data cleaning.

3. Why do you use the aggregate and table functions in Snowflake?

Ans: We use aggregate functions to combine many rows into a single, summarized value.

4. Can I create custom functions using Snowflake?

Ans: Yes, Snowflake supports user-defined functions (UDFs) in multiple languages. It helps developers bring in complex logic into SQL queries.

5. How to troubleshoot a function that runs slowly?

Ans: You can check the query profile in Snowflake. If you are using external APIs, you can check their function-level latency.

Conclusion

In summary, you have gone through the Snowflake SQL functions tutorial with examples. We hope this tutorial helped you master Snowflake SQL functions and unlock Snowflake’s analytical capabilities.

If you want to learn more about Snowflake functions, you can take a course at MindMajix. After training, you will have a better understanding of Snowflake SQL functions and data warehousing concepts.