# How to Do Cast in Snowflake

By default, Snowflake is not strict with type casting. For example, adding a numeric value in string quotes to another numeric value with not give the usual errors other databases and programming languages will give:

```sql
select 10 + '10';
```

However, should the need arise, you can use the `cast()` function to force the type of a value.

```sql
-- cast float to integer
select cast(1.0123456789 as int);

-- cast string to date
select cast('2020-04-22' as date);

-- cast string to decimal
select cast('12.345' as decimal(5,2));

-- cast string to time
select cast('12:45' as time);
```

You can cast to many Snowflake types such as `binary`, `char`, `varchar`, `date`, `datetime`, `time`, `decimal`, `bigint`, and `float`.

How to Do Type Casting

# How to Use Snowflake Update Statement

Usually you only want to update rows that match a certain condition. You do this by specifying a `WHERE` clause:

```sql
--this will update only one row that matches id = 1
update sessions
set start_date = '2020-04-20 10:12:15.653',
    end_date = '2020-04-22 15:40:30.123'
where id = 1;

--this will update multiple rows that match category = 1
update sessions
set end_date = null
where category = 1;
```

To update all rows in a Snowflake table, just use the `UPDATE` statement without a `WHERE` clause:

```sql
update sessions
set end_date = '2020-04-04 16:57:53.653';
```

You can also update multiple columns at a time:

```sql
update sessions
set start_date = '2020-04-02 14:05:15.400',
    end_date = '2020-04-04 16:57:53.653';
```

How to Update Data

# How to Use Snowflake insert into Command

Here's the shortest and easiest way to insert data into a Snowflake table. You only have to specify the values, but you have to pass all values _in order_. If you have 10 columns, you have to specify 10 values.

```sql
-- assuming the sessions table has only four columns:
-- id, start_date, and end_date, and category, in that order
insert into sessions values (1, '2020-04-02 14:05:15.400', '2020-04-03 14:25:15.400', 1);
```

It's optional, but specifying a column list before the `VALUES` keyword is **highly recommended**:

```sql
insert into sessions (id, start_date, end_date, category)
values (12, '2020-04-02 14:05:15.400', '2020-04-04 16:57:53.653', 2);
```

By specifying a column list, you don't have to remember the column order as defined in the table:

```sql
insert into sessions (id, category, start_date, end_date)
values (3, 2, '2020-04-02 14:05:15.400', '2020-04-04 16:57:53.653');
```

Having a column list has more advantages:

*   You don't have to specify a value for all columns, just the required ones.
*   In case there are many columns, it is easier to match a value to the intended column when you see it in the statement, rather than having to look at the table definition.
*   `INSERT` statements without a column lists are invalidated once a column is added or removed from the table. You need to modify your query to reflect the new or deleted column in order for them to work again.

If you have many columns, but only want to specify some:

```sql
insert into sessions(id, start_date) values (4, '2020-04-02 14:05:15.400');
```

### **Inserting Multiple Rows**

You can insert multiple rows in one `INSERT` statement by having multiple sets of values enclosed in parentheses:

```sql
insert into sessions (id, start_date, end_date, category)
values
  (5, '2020-04-02 15:05:15.400','2020-04-03 15:14:30.400', 3),
  (6, '2020-04-02 17:07:16.300','2020-04-02 19:10:15.400', 4),
  (7, '2020-04-03 15:05:45.127','2020-04-04 18:05:15.400', 2);
```

You can also use `CREATE TABLE` with a `SELECT` command to copy data from an existing table. Note the `VALUES` keyword is omitted:

```sql
--ommiting a cloumn list specification
create table sessions_dm_1 as
select *
from sessions
where id <= 5;

--specifying a column list
create table sessions_dm_2 (id, start_date, end_date, category) as
select *
from sessions
where id > 5;
```

### **Inserting JSON Values**

If you want to insert into a JSON column, just wrap the valid JSON in a single quoted string:

```sql
insert into sessions(dates) values('{ "start_date": "2020-04-02 14:05:15.400", "end_date": "2020-04-02 14:57:45.127" }');
```

See also: our tutorial on [querying JSON columns](https://popsql.com/learn-sql/snowflake/how-to-query-a-json-object-in-snowflake/).

### **Handling Conflicts/Duplicates**

If inserting a row would violate a unique constraint, there is a way to handle it.

```sql
--if you want to insert the row if doesn't exists, otherwise ignore
insert into sessions(id, dates)
select
  1,
  '{ "start_date": "2020-04-02 14:05:15.400", "end_date": "2020-04-04 14:57:45.127" }'
where not exists (select id from sessions where id=1);

--if you want to update sessions_db_1 row if exists with sessions_db_2 row value, otherwise insert in sessions_db_1:
merge into sessions_db_1 a using (select id, dates from sessions_db_2 where id=3) as b on a.id=b.id
  when matched then update set a.dates=null --id=3 exists in both tables: sessions_db_1 will be updated with sessions_db_2 row value
  when not matched then insert (id, dates) values (b.id, b.dates); --id=3 exists in sessions_db_2, but doesn't exist in sessions_db_1: the row from sessions_db_2 will be inserted into sessions_db_1
```

How to Insert Data

# How to Delete Data in Snowflake

To delete rows in a Snowflake table, use the `DELETE` statement:

```sql
delete from sessions where id = 7;
```

The `WHERE` clause is optional, but you'll usually want it, unless you really want to delete every row from the table.

```sql
delete from sessions;
```

How to Delete Data

# How to Add a Default Value to a Column in Snowflake

Adding a default value to a table column in Snowflake can be tricky. Unfortunately, we can't add a default for an existing column or modify it unless the default value is the sequence.

To modify a default for a column which already contains a sequence default, use the `ALTER TABLE <table_name> ALTER <column_name> SET DEFAULT` command:

```sql
-- example: default id values changed from one sequence to another
alter table products
alter id
set default id_seq.nextval;
```

How to Add a Default Value to a Column

# How to Add a Column in Snowflake using Alter Table

Adding a column in Snowflake involves using the `ALTER TABLE` command.

Adding a brand\_id smallint column:

```sql
alter table products
add brand_id smallint;
```

Adding a brand\_id smallint column with a default value:

```sql
alter table products
add column brand_id smallint default 1;
```

Adding a string (varchar) column with a not null constraint:

```sql
-- note: this is possible only if the table contains no data!
alter table products
add description varchar(100) not null;
```

Adding more columns at the same time:

```sql
alter table products
add
  brand_id smallint default 1,
  description varchar(100) not null;
```

How to Add a Column

# How to Add a NOT NULL Constraint in Snowflake

Not null constraints add another layer of validation to your data.

While you could perform this validation in your application layer, be aware that inconsistencies will happen: someone will forget to add the validation, or remove it by accident, or bypass validations in a console and insert nulls, etc. The only sure way is to enforce it in your column definition. If you're validating nulls on the database layer as well, you're protected.

To enforce `NOT NULL` for a column in Snowflake, use the `ALTER TABLE <table_name> ALTER <column_name>` command and restate the column definition, adding the `NOT NULL` attribute.

```sql
alter table products
alter category not null;
```

How to Add a NOT NULL Constraint

# How to Alter Sequence in Snowflake

Auto-incrementing columns start at 1 by default. Sometimes you want them to start at a different number and/or increment by a different amount. These numbers are known as “sequences”. Here is how to create them in Snowflake:

```sql
-- syntax
create sequence sequence_name
start = number
increment = number;
```

Some examples:

```sql
create sequence even_numbers
start = 2
increment = 2;

create sequence negative_numbers
start = 0
increment = -1; -- sequence that increments backward
```

To alter the sequence so that IDs start with a different number, you can't just do an `UPDATE`. You have to use the `ALTER SEQUENCE` command:

```sql
--change the increment number of a sequence
alter sequence even_numbers
set increment = 10;

--set a comment for a sequence
alter sequence even_numbers
set comment = 'even dozens';

--rename a sequence
alter sequence even_numbers rename to even_numbers_dozens;
```

How to Alter Sequence

# How to Create a Table in Snowflake

Here's an example of creating a `users` table in Snowflake:

```sql
create table users (
  id integer autoincrement, -- auto incrementing IDs
  name varchar (100),  -- variable string column
  preferences variant, -- column used to store JSON type of data
  created_at timestamp
);
```

Within the parentheses are what's called _column definitions_, separated by commas. The minimum required fields for a column definition are column name and data type (shown above for columns `name`, `preferences`, and `created_at`). The `id` column has extra field to use an auto-incrementing feature to assign it values.

This is also a chance to specify [not null constraints](https://popsql.com/learn-sql/snowflake/how-to-add-a-not-null-constraint-in-snowflake/) and [default values](https://popsql.com/learn-sql/snowflake/how-to-add-a-default-value-to-a-column-in-snowflake/):

```sql
create table users (
  id integer autoincrement,
  name varchar(100) not null,
  active boolean default true
);
```

You can also create temporary tables that will stick around for the duration of your session. This is helpful to break down your analysis into smaller pieces.

```sql
create temporary table inactive_users (
  id integer autoincrement,
  name varchar(100) not null,
  active boolean default false
);
```

Snowflake allows us to create transient tables which are a mix of permanent and temporary tables. They are used to store temporary data outside our session without having the need to implement a high level of data security and data recovery.

```sql
create transient table active_users (
  id integer autoincrement,
  name varchar(100) not null,
  active boolean default true
);
```

How to Create a Table

# How to Create a View in Snowflake

Views let you to encapsulate or “hide” complexities, or allow limited read access to part of the data.

To create a view, use the `CREATE VIEW` command:

```sql
-- syntax
create view view_name
as select_statement;
```

Some examples:

1.  a view to show only products within category 1

```sql
create view category_1_products_v as
select *
from products
where category = 1;
```

2.  a view to limit read access to only certain columns

```sql
create view category_products_basic_v as
select
  name,
  category,
  unit_price
from products;
```

3.  a view that displays top 10 products that provide highest sold value

```sql
create view top_10_products_v as
select
  top 10 p.name,
  p.category,
  p.unit_price,
  ps.quantity_sold,
  p.unit_price * ps.quantity_sold as sold_value
from products p
left join products_sold ps on p.id = ps.product_id
order by sold_value desc;
```

How to Create a View

# How to Create an Index in Snowflake with Clustering Keys

Let's immediately clarify one thing: Snowflake doesn't support indices. Instead of creating or dropping an index in Snowflake, you can use clustering keys to accomplish query performance. This tutorial will show you how to define a clustering key for a particular table.

To create clustering key, use the `ALTER TABLE .. CLUSTER BY` command:

```sql
-- syntax
alter table table_name cluster by (column1, column2, .., columnN);

-- create clustering key on one column
alter table active_users
cluster by (id)

-- create clustering on multiple columns
alter table active_users
cluster by (id, active)
```

However, you should be very careful about using clustering keys. Clustering keys should be used only in case you are dealing with tables of the following properties:

*   Tables containing huge amount of data (i.e. measured in terabytes).
*   Data from tables are filtered on a regular basis.

Note: Unlike other technologies that allow us creation of multiple indices on table, Snowflake lets us create only one clustering key per table!

How to Create an Index

# How to Drop a Column in Snowflake

Dropping a column in Snowflake involves using the `ALTER TABLE .. DROP COLUMN` command.

Drop one column:

```sql
alter table products
drop column description;
```

Drop multiple columns at the same time:

```sql
alter table products
drop column price, description;
```

How to Drop a Column

# How to Drop a Table in Snowflake

Dropping a table in Snowflake is simple:

```sql
drop table users;
```

Unlike other technologies, Snowflake offers the "undo" option for this command. If you want to restore the table, you can do it using the `undrop table` command:

```sql
undrop table users;
```

With the `undrop table` command, data that resided in the table will also be restored.

How to Drop a Table

# How to Drop a View in Snowflake

To drop a view, use the `DROP VIEW` command:

```sql
drop view category_1_products_v;
```

How to Drop a View

# How to Drop an Index in Snowflake

As previously mentioned, Snowflake doesn't support the concept of indices.

As a substitute in particular situations, you can use clustering keys. Creation of clustering keys is explained [here](https://popsql.com/learn-sql/snowflake/how-to-create-an-index-in-snowflake/), and this article will show you how to drop a clustering key for a particular table.

To drop a clustering key, use the `ALTER TABLE .. DROP CLUSTERING KEY` command:

```sql
-- syntax
alter table table_name drop clustering key;

alter table active_users drop clustering key;
```

How to Drop an Index

# How to Duplicate a Table in Snowflake

Sometimes you need to duplicate a table. There are various methods, depending on your intent.

Copy both the entire table structure and all the data inside:

```sql
--method 1
create table sessions_copy clone sessions;

--method 2
create table sessions_copy as
select *
from sessions;
```

Copy entire table structure along with particular data set:

```sql
create table sessions_db_1_copy as
select *
from sessions_db_1
where dates is null;
```

Copy only particular columns into new table along with particular data set:

```sql
create table sessions_dm_1_copy as
select
  id,
  start_date,
  end_date
from sessions_dm_1
where category = 2;
```

Copy only particular columns from more tables into new table along with particular data set:

```sql
create table users_sessions_1_rpt as
select
  u.name,
  s.start_date as session_start_date,
  s.end_date as session_end_date
from sessions s
left join user_sessions us on s.id = us.session_id
left join users_1 u on us.user_id = u.id
where u.active = true;
```

Copy only table structure, no data copying:

```sql
create table users_copy like users;
```

How to Duplicate a Table

# How to Remove a Default Value to a Column in Snowflake

To remove a default value to a column in Snowflake, use the `ALTER TABLE <table_name> ALTER <column_name> DROP DEFAULT` command:

```sql
alter table products
alter id
drop default;
```

How to Remove a Default Value to a Column

# How to Remove a NOT NULL Constraint in Snowflake

To remove a `NOT NULL` constraint for a column in Snowflake, you use the `ALTER TABLE <table_name> ALTER <column_name> DROP` command and restate the column definition, adding the `NOT NULL` attribute.

```sql
alter table products
alter category drop not null;
```

How to Remove a NOT NULL Constraint

# How to Rename a Column in Snowflake

Renaming a column in Snowflake involves using the `ALTER TABLE .. RENAME COLUMN` command.

```sql
-- syntax
alter table table_name rename column old_name to new_name;

-- rename product_category column in products table
alter table products rename column products_category to products;
```

How to Rename a Column

# How to Rename a Table in Snowflake

Renaming a table in Snowflake is performed by using `ALTER TABLE .. RENAME TO` statement:

```sql
--the syntax
alter table old_table_name rename to new_table_name;

--rename users_marketing to users table
alter table sessions_db1 rename to sessions_db_1;
```

How to Rename a Table

# How to Truncate a Table in Snowflake

Be super careful with `TRUNCATE` command. It will empty the content of your Snowflake table. This is useful in development, but you'll seldom want to do this in production.

```sql
--syntax
truncate table_name;

truncate users;
```

If the table contains an `AUTO_INCREMENT` column, the counter for it will not get reset. Note that this behavior is different vs some other databases like [SQLServer](https://popsql.com/learn-sql/sql-server/how-to-alter-sequence-in-sql-server/) for example, where you don't need to reset the counter yourself.

How to Truncate a Table

# How to Convert UTC to Local Time Zone in Snowflake

When storing timestamps, Snowflake stores time zone data in the form of adding the offset at the end of the timestamp. That offset code tells us the time zone of timestamps.

Snowflake uses the host server time as the basis for generating the output of `current_timestamp()`.

Default timezone in Snowflake is Pacific Daylight Time (PDT). To convert a PDT timestamp to a UTC or a local time zone, you can use the following:

```sql
select
  current_timestamp() as pdt_time_zone,
  convert_timezone('UTC', current_timestamp()) as utc_time_zone,
  convert_timezone('Europe/Paris', current_timestamp()) as europe_paris_time_zone
```

> _Note: Snowflake supports_ [_IANA_](https://www.iana.org/time-zones) _time zone database._

How to Convert UTC to Local Time Zone

# How to Group by Time in Snowflake

When you need to group by minute, hour, day, week, etc., you may think you can simply group by your timestamp column.

If you do that, though, you'll get one group per second -- probably not what you want. Instead you need to “truncate” your timestamp to the granularity you want, like minute, hour, day, week, etc. The function you need here is`date_trunc()`:

```sql
-- returns number of sessions grouped by particular timestamp fragment
select
  date_trunc('DAY',start_date), --or WEEK, MONTH, YEAR, etc
  count(id) as number_of_sessions
from sessions
group by 1
;
```

How to Group by Time

# How to Query Date and Time in Snowflake

Get the date and time right now (where Snowflake is running):

```sql
select current_timestamp;
select getdate();
select systimestamp();
select localtimestamp;
```

Find rows between two dates or timestamps:

```sql
select *
from events
where event_date between '2020-04-18' and '2020-04-20';

-- can include time by specifying in YYYY-MM-DD hh:mm:ss format:
select  *
from events
where event_date between '2020-04-18 12:00:00' and '2020-04-20 23:30:00';
```

> _Note: always put the dates in the 'between .. and' statement in ascending order(from smaller to larger date value)_

Find rows created within the last week:

```sql
select *
from events
where event_date > (select dateadd(week, -1, getdate()));
```

Find events scheduled between one week ago and 3 days from now:

```sql
select *
from events
where event_date between (select dateadd(week, -1, getdate())) and (select dateadd(day, +3, getdate()));
```

Extracting part of a timestamp and returning an integer:

```sql
select day(getdate()); -- or month() or year()
select date_part(hour, getdate()); -- or hour, week, month, quarter, year
```

Extracting part of a timestamp and returning a string (e.g. "Feb" or "Mon"):

```sql
select dayname(getdate());
select monthname(getdate());
```

Get the day of the week from a timestamp:

```sql
select date_part(weekday, getdate()); -- returns 0-6 (integer), starting with 0 as Sunday
```

How to convert a timestamp to a unix timestamp:

```sql
select datediff(second, '1970-01-01' , current_timestamp())
```

To calculate the difference between two timestamps, convert them to unix timestamps then subtract:

```sql
select datediff(second, '1970-01-01' , '2020-04-20 00:00:00.000 -0700') - datediff(second, '1970-01-01
```

How to Query Date and Time

# How to Round Timestamps in Snowflake using date\_trunc Function

Rounding and/or truncating timestamps is useful when you're [grouping by time](https://popsql.com/learn-sql/snowflake/how-to-group-by-time-in-snowflake/). There are a few approaches.

## The DATE\_TRUNC function

```sql
select date_trunc('second', now()) -- or minute, hour, day, month
```

## Predefined functions in Snowflake

If you are rounding by year, you can use the `year()` function (or `month()`, `week()`, `day()`, etc:

```sql
select year(getdate()) as year;
```

Be careful though. Using the `month()` function will, for example, make January 2020 and January 2019 both just translate to `1`. That may not be what you want

## Round and format result as a string, e.g. '05-2020'

However, if you want to distinguish between months of different years, you need to use `to_varchar()` function:

```sql
select to_varchar(getdate(), 'mm-yyyy'); -- round to month
select to_varchar(getdate(),'dd-mm-yyyy'); -- round to day
select to_varchar(getdate(),'dd-mm-yyyy hh'); -- round to hour
select to_varchar(getdate(),'dd-mm-yyyy hh:mm'); -- round to minute
select to_varchar(getdate(),'dd-mm-yyyy hh:mm:ss'); -- round to second
```

How to Round Timestamps

# How to Avoid Gaps in Data in Snowflake

Let's say you need to group by time in Snowflake but you don't want any gaps in your report data.

First, generate a series of date/time values that have no gaps using a [common table expression](https://popsql.com/learn-sql/snowflake/how-to-write-a-common-table-expression-in-snowflake/):

```sql
set start_date = '2020-04-01';
set end_date = '2020-04-30';

with cte_date (date_rec) as (
  select to_date($start_date)
  union all
  select to_date(dateadd(day, 1, date_rec)) --or week, month, week, hour, minute instead of day
  from  cte_date
  where  date_rec < $end_date
)
select date_rec
from cte_date;
```

> Note: you can [run multiple statements](https://popsql.com/docs/getting-started/writing-a-query/) in sequence in PopSQL with just one click. In the example above, you could highlight all 3 statements at once and then click `Run All`.

```sql
date_rec
------------------------
2020-04-01
2020-04-02
2020-04-03
2020-04-04
...
2020-04-30
```

Now you can left join your date series data against this gapless series. Here's how you could create a count of sessions for each day:

```sql
set start_date = '2020-04-01';
set end_date = '2020-04-30';

with cte_date (date_rec) as (
  select  to_date($start_date)
  union all
  select  to_date(dateadd(day, 1, date_rec))
  from  cte_date
  where  date_rec < $end_date
)

select
  cte_date.date_rec,
  count(s.id) as session_ct
from cte_date
left outer join sessions s on to_date(s.start_date) = cte_date.date_rec
group by date_rec;
```

How to Avoid Gaps in Data

# How to Calculate Cumulative Sum/Running Total in Snowflake

Let's say we want a graph that's always "up and to the right" in Snowflake. Say, a graph of our cumulative sessions by day. First, we'll need a table with a day column and a count column:

```sql
select
  to_date(start_date) as day,
  count(1)
from sessions
group by to_date(start_date);
```

```sql
     day    | count
------------+-------
 2020-05-01 | 1
 2020-05-02 | 4
 2020-05-03 | 2
```

Next, we'll write a [Snowflake common table expression (CTE)](https://popsql.com/learn-sql/snowflake/how-to-write-a-common-table-expression-in-snowflake/) and use a window function to keep track of the cumulative sum/running total:

```sql
select
  to_date(start_date) as day,
  count(1)
from sessions
group by to_date(start_date);

with data as (
  select
    to_date(start_date) as day,
    count(1)  as number_of_sessions
  from sessions
  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;
```

```sql
      day    | sum
------------+-------
 2020-05-01 | 1
 2020-05-02 | 5
 2020-05-03 | 7
```

Voila! 📈

How to Calculate Cumulative Sum/Running Total

# How to Calculate Percentiles in Snowflake

Let's say you want to look at the percentiles for products. You can use Snowflake's `percentile_cont()` function to do that:

```sql
select
  percentile_cont(0.25) within group(order by unit_price) over () as p25,
  percentile_cont(0.50) within group(order by unit_price) over () as p50,
  percentile_cont(0.75) within group(order by unit_price) over () as p75,
  percentile_cont(0.95) within group(order by unit_price) over () as p95
from products;
```

If you want to view those percentiles by category:

```sql
select distinct category,
  percentile_cont(0.25) within group(order by unit_price) over (partition by category) as p25,
  percentile_cont(0.50) within group(order by unit_price) over (partition by category) as p50,
  percentile_cont(0.75) within group(order by unit_price) over (partition by category) as p75,
  percentile_cont(0.95) within group(order by unit_price) over (partition by category) as p95
from products
order by category;
```

How to Calculate Percentiles

# How to Compare Two Values When One is NULL in Snowflake

Say you're comparing two Snowflake columns and you want to know how many rows are different. No problem, you think:

```sql
select count(1)
from sessions
where start_date != end_date;
```

Wait a minute. If some of the start or end dates are null, they won't be counted! 😬 That's definitely not what you wanted. That's why you need to use the `INTERSECT` operator:

```sql
select count(1)
from sessions
where not exists(select start_date from sessions intersect select end_date from sessions);
```

Now, your count will be “null aware” and you'll get the result you want.

How to Compare Two Values When One is NULL

# How to Get First Row Per Group in Snowflake using row\_number Function

Let's say you have tables that contain data about users and sessions, and you want to see the first session for each user for particular day. The function you need here is `row_number()`. Here's an example of how to use it:

```sql
select
  us.user_id,
  us.session_id,
  s.start_date,
  s.end_date,
  row_number() over (partition by user_id order by start_date desc) as row_number
from user_sessions us
left outer join sessions s on s.id = us.session_id
where to_varchar(start_date,'dd-mm-yyyy') = '02-04-2020';
```

This gives you all the session IDs for the day, plus their row number. Since you only want the first session for the day, you only want rows that have row\_number: 1. To do that, you can use a [common table expression](https://popsql.com/learn-sql/snowflake/how-to-write-a-common-table-expression-in-snowflake/):

```sql
with cte_sessions as (
  select
    us.user_id,
    us.session_id,
    s.start_date,
    s.end_date,
    row_number() over (partition by user_id order by start_date desc) as row_number
  from user_sessions us
  left outer join sessions s on s.id = us.session_id
  where to_varchar(start_date,'dd-mm-yyyy') = '02-04-2020'
)
select *
from cte_sessions
where row_number = 1;
```

How to Get First Row Per Group

# How to Have Multiple Counts in Snowflake

To do multiple counts in one query in Snowflake, you can combine `sum()` with the [CASE statement](https://popsql.com/learn-sql/snowflake/how-to-write-a-case-statement-in-snowflake/):

```sql
select
  count(1), -- count all products
  sum(case when name = 'Table' then 1 else 0 end), -- count all tables
  sum(case when category = 4 then 1 else 0 end) -- count all category 4 products
from products
```

How to Have Multiple Counts

# How to Upload CSV to Snowflake

Snowflake allows you to upload a CSV file from your local machines that run on Windows, Linux, or MacOS.

This tutorial will show you how to upload a CSV file from all three platforms to a Snowflake database table.

In this example, the CSV file to be imported is called `Enterprises`. It contains three columns (`id`, `name`, and `location`), is located in "test" folder of our local machine, and has the following structure:

```sql
 1,Microsoft,Washington
 2,Apple,California
 3,IBM,New York
 ...
```

Steps:

1.  Create a Snowflake stage

```sql
create or replace stage enterprises_stage;
```

2.  Create a file format using the `FILE FORMAT` command to describe the format of the file to be imported

```sql
create or replace file format enterprises_format type = 'csv' field_delimiter = ',';
```

3.  Upload your CSV file from local folder to a Snowflake stage using the `PUT` command

```sql
-- this step can not be performed by running the command from the Worksheets page on the Snowflake web interface. You'll need to install and use SnowSQL client to do this

-- Windows
put file://C:\test\Enterprises.csv @enterprises_stage;

-- Linux/Mac
put file:///tmp/data/Enterprises.csv @enterprises_stage;
```

4.  Check to see if the Snowflake stage is populated with the data from the file

```sql
select
  c.$1,
  c.$2,
  c.$3
from @enterprises_stage (file_format => enterprises_format) c;
```

5.  You need to create a table within Snowflake database that has the same structure as the CSV file we want to import prior to running the `COPY INTO` command.

```sql
create or replace table enterprises (
  id integer,
  name varchar (100),
  location varchar(100)
)
```

6.  Load data from a Snowflake stage into a Snowflake database table using a `COPY INTO` command

```sql
-- load data as it is organized in a CSV file
copy into test.enterprises from @enterprises_stage;

-- if you want to filter out data from a stage and import only particular columns
copy into test.enterprises from (select c.$1, c.$2 from @enterprises_stage (file_format => enterprises_format) c);
```

7.  Check to see if the Snowflake database table is populated with the data

```sql
select * from enterprises;

      id    | name        | location
------------+-------------+---------------
 1          | Microsoft   | Washington
 2          | Apple       | California
 3          | IBM         | New York
```

How to Upload CSV

# How to Query a JSON Object in Snowflake using parse\_json Function

Snowflake supports querying JSON columns. This gives the advantage of storing and querying unstructured data. Here's how you can query a JSON column in Snowflake.

Get only `salesperson.name` from the employees table:

```sql
--level 2 element: get salesperson.name from the customers table
select parse_json(text):salesperson.name as sales_person_name
from customers
```

Get only customers related to a particular sales person:

```sql
select *
from customers
where parse_json(text):salesperson.name='Alice Miller'
```

Get first key and its value of a JSON data set:

```sql
select top 1 parse_json(text):customer as customer_key
from customers

--going deeper to get only customer name
select top 1 parse_json(text):customer.name as customer_name_key
from customers
```

Get JSON keys as columns populated with key values:

```sql
select
  parse_json(text):customer.name as customer_name, -- level 2 element having one value
  parse_json(text):customer.address as customer_address,
  parse_json(text):customer.phone as customer_phone,
  parse_json(text):dealership as dealership, -- level 1 element having one value
  parse_json(text):salesperson.id as sales_person_id,
  parse_json(text):salesperson.name as sales_person_name,
  parse_json(text):vehicle.extras[0] as extras_1, -- first value of level 2 element having three values
  parse_json(text):vehicle.extras[1] as extras_2, -- second value of level 2 element having three values
  parse_json(text):vehicle.extras[2] as extras_3  -- third value of level 2 element having three values
from customers
```

How to Query a JSON Object

# How to Use Coalesce in Snowflake

Imagine you're looking at a Snowflake integer column where some rows are null:

```sql
select day, tickets
from stats;
```

```sql
    day     | tickets
------------+---------
 2020-05-01 | 1
 2020-05-02 | null
 2020-05-03 | 3
```

Instead of having that null, you might want that row to be 0. To do that, use the `coalesce()` function, which returns the first non-null argument it's passed:

```sql
select day, coalesce(tickets, 0)
from stats;
```

```sql
    day     | tickets
------------+---------
 2020-05-01 | 1
 2020-05-02 | 0
 2020-05-03 | 3
```

How to Use Coalesce

# How to Write a Snowflake Case When

Case statements are useful when you're reaching for an if statement in your `select`clause.

```sql
select
  id,
  name,
  category,
  unit_price,
  case
    when category = 5 then 'Premium'
    when category = 4 then 'Gold'
    when category = 3 then 'Standard'
    when category <= 2 then 'Basic'
    else 'unknown'
  end as quality_level
from products;
```

How to Write a Case Statement

# How to Write a Common Table Expression in Snowflake

Common table expressions (CTEs) are a great way to break up complex queries. Snowflake also supports this functionality. Here's a simple query to illustrate how to write a CTE:

```sql
with free_users as (
  select *
  from users
  where plan = 'free'
)
select user_sessions.*
from user_sessions
inner join free_users on free_users.id = user_sessions.user_id
order by free_users.id;
```

You can find more complex examples of using CTE's in [How to Avoid Gaps in Data in Snowflake](https://popsql.com/learn-sql/snowflake/how-to-avoid-gaps-in-data-in-snowflake/) and in [Calculating Cumulative Sums in Snowflake](https://popsql.com/learn-sql/snowflake/how-to-calculate-cumulative-sum-running-total-in-snowflake/).

Snowflake

Ready for a modern SQL editor?