When jumping from one project to another, it can be useful to be able to compare common code structures.I couldn’t find anything like this out there, so here it is magically created. Contents:Common Code StructuresWorking with DatesWindow FunctionsError HandlingCastingJoining TablesCTE (Common Table Expressions) Common Code Structures Topic SQL Server Snowflake BigQuery Select & Filtering SELECT… Read more: SQL Code comparisons: SQL Server, Snowflake, BigQuery

Summary 1. Aggregate Window FunctionsThese functions perform calculations across a set of table rows that are somehow related to the current row. SUM() OVER(…) – Running total or sum per partitionAVG() OVER(…) – Average per partitionCOUNT() OVER(…) – Count rows in partitionMIN() OVER(…) – Minimum value in partitionMAX() OVER(…) – Maximum value in partition 2.… Read more: SQL Window Functions

We can create a more interesting sales report using Window functions and additional calculations in the query, including the gross margin %, total profit, and ranks for product price, sales quantity, and sales rank. The query uses a Common Table Expression (CTE) function to group the data first (as we can’t group using Windows functions).… Read more: An Example of Creating a Sales Report with SQL

References: Date Functionshttps://cloud.google.com/bigquery/docs/reference/standard-sql/date_functionsFormat Elementshttps://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_elements_date_timeCurrent Date, Last_Date, Construction Add and Subtract FORMAT_DATE and PARSE_DATE EXTRACT DATE_DIFF DATE() Constructor DATE TRUNC()

The first part of building the code to pull the data for the lifetime value model is to create an SQL script that will get the historical data we need to train our model on. The SQL Code select 6 fields from the Contoso Retail Datawarehouse:The full SQL code is at the bottom of the… Read more: SQL Code for Lifetime Value Model

There are three steps to get up and running with a Contoso Retail Datawarehouse that you can use for experimenting with Power BI. Step 1. If you don’t already have access to an installation of Microsoft SQL server you can install one on your local machine. There are 2 free non-production versions available: Microsoft SQL… Read more: How to install the Contoso Retail Database on SQL Server

For installing and getting started with the Contoso Retail Database, please see this article. A standard starting point for creating customer insight reports is to combine data from sales with your customer table. In the Contoso Retail DW, we have the FactSales and DimCustomer tables that we can use. The DimCustomer Table has a field… Read more: An Example investigation of the Contoso Data Warehouse Tables

The learn power bi we need data to work with. The Contoso Retail DW database from Microsoft is freely available, so we’ll use that one. You could just install a copy to a local desktop version of SQL Express.A description of the dataset from Microsoft is as follows: ‘The Contoso BI Demo dataset is used… Read more: An Overview of the Contoso Retail Datawarehouse Tables