Other Snowflake SQL Fxns

How do you calculate percent of total in SQL without losing row detail? You want to know, for example, if a single row is emergency and also (on a row level) what the % emergency is across the dataset

Use SUM(value) OVER () to compute the total across all rows and divide each row by it (e.g., sales / SUM(sales) OVER () gives percent of total). SUM(emergency) OVER () / COUNT(*) OVER () AS pct_emergency_claims
Technically, you could also use AVG(emergency) over ()

for example, this would fail:
SELECT

claim_id,

emergency,

AVG(emergency) * 100 AS pct_emergency_claims

FROM claims;

because claim_id, emergency need to be in group by

But this would succeed:
SELECT

claim_id,

emergency,

AVG(emergency) over () * 100 AS pct_emergency_claims

FROM claims;

How do you calculate a running total in Snowflake SQL?

Use SUM(value) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) to accumulate values row by row without collapsing rows.

How do you rank rows within a group in SQL?

Use RANK() or DENSE_RANK() OVER (PARTITION BY group_col ORDER BY metric DESC) since ranking requires comparing rows without grouping them away.

How do you compare a row to its group average in SQL?

Use AVG(value) OVER (PARTITION BY group_col) to attach the group average to each row for direct comparison.

How do you get the max or min of a group while keeping all rows? On every row I want the min/max of that group column

Use MAX(value) OVER (PARTITION BY group_col) to show the group’s extreme value on every row.

Why can’t GROUP BY replace OVER() in certain cases?

GROUP BY collapses rows and removes row-level context

Calculate the average of numeric values across rows

AVG(expr) | AVG(expr) OVER ( [PARTITION BY expr] [ORDER BY expr] [ROWS|RANGE frame] ) | Example: AVG(salary) OVER (PARTITION BY dept ORDER BY hire_date)

Count rows or non-null values

COUNT() | COUNT(expr) | COUNT(expr) OVER (PARTITION BY expr) | Example: COUNT() OVER (PARTITION BY dept)

Return the total sum of numeric values

SUM(expr) | SUM(expr) OVER (PARTITION BY expr ORDER BY expr) | Example: SUM(revenue) OVER (PARTITION BY region)

Return the minimum value from a set

MIN(expr) | MIN(expr) OVER (PARTITION BY expr ORDER BY expr) | Example: MIN(price) OVER (PARTITION BY category)

Return the maximum value from a set

MAX(expr) | MAX(expr) OVER (PARTITION BY expr ORDER BY expr) | Example: MAX(score) OVER (PARTITION BY class)

Assign a unique sequential row number within a partition

ROW_NUMBER() OVER (PARTITION BY expr ORDER BY expr) | Example: ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)

Rank rows with gaps when ties occur

RANK() OVER (PARTITION BY expr ORDER BY expr) | Example: RANK() OVER (PARTITION BY dept ORDER BY salary DESC)

Rank rows without gaps when ties occur

DENSE_RANK() OVER (PARTITION BY expr ORDER BY expr) | Example: DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC)

Return the first value in an ordered window

FIRST_VALUE(expr) OVER (PARTITION BY expr ORDER BY expr frame) | Example: FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY hire_date)

Return the last value in an ordered window

LAST_VALUE(expr) OVER (PARTITION BY expr ORDER BY expr ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Access a value from a prior row

LAG(expr

Access a value from a following row

LEAD(expr

Divide rows into buckets

NTILE(n) OVER (ORDER BY expr)

Compute cumulative distribution

CUME_DIST() OVER (PARTITION BY expr ORDER BY expr)

Compute relative rank as a percentile

PERCENT_RANK() OVER (PARTITION BY expr ORDER BY expr)

Return the absolute value of a number

ABS(expr)

Round a number to a specified scale

ROUND(expr

Round a number up

CEIL(expr) | CEILING(expr)

Round a number down

FLOOR(expr)

Return the remainder of division

MOD(expr1

Raise a number to a power

POWER(expr

Return the square root of a number

SQRT(expr)

Return a random number between 0 and 1

RANDOM()

Return the exponential value

e^x

Return the natural logarithm

LN(expr)

Return logarithm with base 10

LOG10(expr)

Return logarithm with specified base

LOG(base

Convert text to uppercase

UPPER(expr)

Convert text to lowercase

LOWER(expr)

Capitalize the first letter of each word

INITCAP(expr)

Remove leading and trailing characters

TRIM(expr) | TRIM(char FROM expr)

Remove leading characters

LPAD(expr

Remove trailing characters

RPAD(expr

Return string length

LENGTH(expr)

Extract part of a string

SUBSTR(expr

Replace text within a string

REPLACE(expr

Concatenate strings

CONCAT(expr1

Split string into array

SPLIT(expr

Search for substring position

POSITION(substring IN expr)

Return the current date

CURRENT_DATE()

Return the current timestamp

CURRENT_TIMESTAMP()

Add time to a date or timestamp

DATEADD(unit

Calculate difference between dates

DATEDIFF(unit

Truncate a date or timestamp

DATETRUNC(unit

Extract date part from date or timestamp

EXTRACT(part FROM date_expr)

Convert value to date

TO_DATE(expr

Convert value to timestamp

TO_TIMESTAMP(expr

Return last day of a period

LAST_DAY(date_expr

Return next specified weekday

NEXT_DAY(date_expr

Return first non-null value

COALESCE(expr1

Return null if expressions are equal

NULLIF(expr1

Conditional logic evaluation

CASE WHEN condition THEN result ELSE result END

Test for null value

expr IS NULL

Test for non-null value

expr IS NOT NULL

Return value based on condition

IFF(condition

Extract value from JSON object

GET(expr

Flatten arrays or objects into rows

FLATTEN(input => expr)

Check if JSON key exists

OBJECT_HAS_KEY(expr

Return JSON object size

OBJECT_SIZE(expr)

Return array size

ARRAY_SIZE(expr)

Convert a value to another data type explicitly

CAST(expr AS datatype)

Convert value to numeric type

TO_NUMBER(expr

Convert value to varchar

TO_VARCHAR(expr)

Convert value to boolean

TO_BOOLEAN(expr)

Convert value to binary

TO_BINARY(expr

Convert value to floating-point number

TO_DOUBLE(expr)

Safely convert value without error

TRY_CAST(expr AS datatype)

Safely convert to number returning null on failure

TRY_TO_NUMBER(expr)

Safely convert to date returning null on failure

TRY_TO_DATE(expr)

Safely convert to timestamp returning null on failure

TRY_TO_TIMESTAMP(expr)

Check if value is within a range

expr BETWEEN low AND high

Check if value matches any in a list

expr IN (value1

Check if value does not match list

expr NOT IN (value1

Check if condition is true for all rows

ALL(subquery)

Check if condition is true for any row

ANY(subquery)

Evaluate logical AND condition

expr AND expr

Evaluate logical OR condition

expr OR expr

Negate a boolean condition

NOT expr

Match text using SQL pattern matching

expr LIKE pattern

Match text using regex

REGEXP_LIKE(expr

Extract substring using regex

REGEXP_SUBSTR(expr

Replace text using regex

REGEXP_REPLACE(expr

Count regex matches

REGEXP_COUNT(expr

Return position of regex match

REGEXP_INSTR(expr

Create an array from values

ARRAY_CONSTRUCT(expr1

Append value to array

ARRAY_APPEND(array

Remove value from array

ARRAY_REMOVE(array

Check if array contains value

ARRAY_CONTAINS(value

Access array element by index

array[index]

Flatten array elements into rows

FLATTEN(input => array)

Create object from key-value pairs

OBJECT_CONSTRUCT(key

Retrieve value by key from object

object:key

Insert or update key-value pair

OBJECT_INSERT(object

Delete key from object

OBJECT_DELETE(object