SQL Code Snippets

SQL Code Snippets

See below for some examples of common SQL transformations that can be applied to Tellius. Tellius utilizes SparkSQL as it's SQL variant of choice. To see how to use the Tellius SQL editor, check out the SQL Transform article.

Convert time column to UTC
select *, to_utc_timestamp(origin_timestamp, origin_timezone) as time_utc

In the above example, we are using to_utc_timestamp built in function to convert the column origin_timestamp to origin_timezone
Calculating Lag/Lead
There can be scenarios where we want to compare our values (Eg Sales) to prior week / month / year or post week /month / year. In such cases taking lag / lead of value will help to understand the absolute change or the percent change. The below example shows how to do the same.

select *, LAG(Sales) over (partition by Order_Id order by Date) from table_name

In the above example, we are using LAG built in function to get the prior Sales value within each Order_Id for every week / month / year.

select *, LEAD(Sales) over (partition by Order_Id order by Date) from table_name

In the above example, we are using LEAD built in function to get the post Sales value within each Order_Id for every week / month / year.
Creating Rolling Average
There can be scenarios where we want to see the prior or two weeks/ month rolling average to monitor the performance. In such cases taking Rolling Average of value will help to understand. The below example shows how to do the same.

select *, AVG(Sales) over (partition by Order_Id Order by Date rows between 2 preceding and 1 preceding) as Two_Weeks_Rolling_Average from table_name

In the above example, we are using AVG built in function to get the two weeks rolling average Sales value within each Order_Id for every week / month / year.

rows between 2 preceding and 1 preceding - This will exclude the current row and take the prior two rows to calculate the rolling average.
Creating Bins/Buckets
There can be scenarios to divide the net sales by month into 4 groups for each product category. In such cases NTILE function assigns a bucket number representing the group for each product category. The below example shows how to do the same.

select product_category_name, month, net_sales,
NTILE(4) OVER(PARTITION BY product_category_name ORDER BY net_sales DESC) net_sales_group from table_name

In above example, we are using NTILE built in function to distribute rows of an ordered partition into a specified number of buckets
Creating Average in the Same Table
There are many instances where we want to compare the sales with the state average sales to track the performance. The below example shows how to do the same.

select a.*, b.State_Sales_Average from table_name a
left join
( select State, AVG(Sales) as State_Sales_Average from table_name group by State )b
on a.State = b.State

In above example, we are using sub query to compute the Average sales for each state and then we are joining it back to the existing table based on State as the Key

Using Case Statements
Select Employee_Name , ( CASE WHEN StateCode = 'AR' then 'FL'
WHEN StateCode = 'GE' then 'AL' ELSE StateCode END) as StateCode from table_name

In the above example, we are updating statecode with the following condition.
-If employee StateCode is AR, then update to FL
-If employee StateCode is GE, then update to AL
-For all other StateCode keep as it is.

Did we help you?

Filter Data

Multiple Datasets Scripting SQL

Contact