alcheng10

BigQuery SQL to get the Adjusted Closing Price for the Last Trading Day in every Quarter (via dimension date table)

WITH 
ASX_DATA AS (
  SELECT 
    'ASX 200 (XJO)' AS Index
    ,DT.Date
    ,DT.Month
    ,DT.Year
    ,DT.Quarter

alcheng10

BigQuery SQL Script to get the rolling and cumulative percentage change for the Average Housing Price by State

WITH PRICES AS (
SELECT 
  Date
  ,State
  ,Residential_Dwelling_Average_Value_AUD AS AVERAGE_HOUSING_PRICE
  ,LAG(Residential_Dwelling_Average_Value_AUD) OVER (PARTITION BY State ORDER BY Date ASC) AS LAG_AVERAGE_HOUSING_PRICE
  ,ROW_NUMBER() OVER (PARTITION BY State ORDER BY Date ASC) AS RNK
FROM transformation.ABS_DWELLING_DATA_TRF

alcheng10

from google.cloud import bigquery
from google.oauth2 import service_account
location = 'australia-southeast1'
project = 'project123'

client = bigquery.Client(project=project, location=location)


def upload_df_to_BQ(df, table_name, mode='append'):

alcheng10

AWS Best Practices Checklist - Serverless

This checklist uses markdown formatting and therefore can be easily incorporated into a git repository's README.md.

General

• Tagging of stack and all resources - e.g. cost tags, microservices, projects
• AWS resources are created via Infrastructure-as-Code (Terraform, Serverless.com, CDK, Pulumi, SAM, etc.)
• Environment variables are passed during deployment (including Stage of stack, such as DEV, PROD, etc.)
• RBAC and Least-Privilege Principle applied - IAM roles limited to only what is needed

alcheng10

SELECT
  RegistrationGUID
  ,RegistrationNo
  ,PolicyNo
  ,MIN(FullDate) AS StartDate
  ,MAX(FullDate) AS EndDate
  ,Cover_YN
  ,COUNT(FullDate) AS NumberDaysCovered

alcheng10

# Example with Dense Rank!
WITH RANKED_DATA AS (
SELECT 
  FullDate
  ,RegistrationGUID
  ,RegistrationNo
  ,PolicyNo
  ,Cover_YN
  ,DENSE_RANK () OVER (

alcheng10

DROP TABLE IF EXISTS DimCustomer_Car_Status;

CREATE TABLE DimCustomer_Car_Status AS
WITH Joined_Data AS (
SELECT 
  o.RegistrationGUID
  ,o.RegistrationNo
  ,o.OwnerName

alcheng10

Daily Date Island Gaps Example

SELECT
  dt.FullDate
  ,c.RegistrationGUID
  ,c.RegistrationNo
  ,c.FromDate AS CarRegistrationFromDate
  ,c.ToDate AS CarRegistrationToDate

alcheng10

# With your date dimension in df_date, join up and filter
data = df_date.join(data.set_index('Date'), how='left')

# Show all empty rows after the LEFT JOIN
data[data.isnull().any(axis=1)]

alcheng10

import pandas as pd
import holidays

def create_dim_date(start_date, end_date):
    '''
    Create Dimension Date in Pandas
    
    :return df_date : DataFrame
    '''