Functions, sorting, and grouping

Introduction to Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

String functions - INITCAP

Syntax: INITCAP( <expr> )

  • Capitalize each word in a string
SELECT INITCAP(category) AS capitalized_category 
FROM pizza_type

Capitalized pizza names result

Introduction to Snowflake SQL

String functions - CONCAT

  • Combines the expressions

Syntax:

CONCAT( <expr1> [ , <exprN> ... ] )

Before Concat:

Before concatenation category column

  • Combining category with ' - Pizza'
    SELECT CONCAT(category, ' - Pizza') 
      AS pizza_category 
    FROM pizza_type
    

After Concat:

Before concatenation category column

Introduction to Snowflake SQL

DATE & TIME functions

  • CURRENT_DATE() or CURRENT_DATE
  • CURRENT_TIME() or CURRENT_TIME
SELECT CURRENT_DATE
SELECT CURRENT_TIME

Current date result

Current time result

Introduction to Snowflake SQL

EXTRACT

Syntax

  • EXTRACT( <date_or_time_part> FROM <date_or_time_expr> )
    • <date_or_time_part> - year, month, day, weekday, etc.
SELECT EXTRACT(MONTH FROM order_date) AS order_month,
    COUNT(*) AS num_orders
FROM orders
GROUP BY order_month

Number of orders per month

Introduction to Snowflake SQL

SORTING and GROUPING

  • SORTING: ORDER BY
  • GROUPING: GROUP BY
    • Snowflake: GROUP BY ALL
Introduction to Snowflake SQL

GROUP BY ALL

  • GROUP BY column1, column2
SELECT 
    pizza_type_id,
    size,
    AVG(price) AS average_price
FROM 
    pizzas
GROUP BY
    pizza_type_id, -- explicit columns
    size
ORDER BY 
    pizza_type_id, average_price DESC

  • GROUP BY ALL

 

SELECT 
    pizza_type_id,
    size,
    AVG(price) AS average_price
FROM 
    pizzas
GROUP BY ALL -- Don't specify columns 
ORDER BY 
    pizza_type_id, average_price DESC
Introduction to Snowflake SQL

Summary

Function/keyword Use
INITCAP() Capitalize each word in a string
CONCAT() Combine multiple strings
CURRENT_DATE Get the current date
CURRENT_TIME Get the current time
EXTRACT Pull a date/time element, e.g., month from a date
ORDER BY Sort query results
GROUP BY ALL Group query by all (non-aggregated) columns
Introduction to Snowflake SQL

Let's practice!

Introduction to Snowflake SQL

Preparing Video For Download...