SQL

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