lfy79001

select top 20 ToUsers.Id as ToUserId
	, ToUsers.UserName as ToUserName
	, case when ToUsers.DisplayName = '' then '<None>' else ToUsers.DisplayName end as ToDisplayName
	, FromUsers.Id as FromUserId
	, FromUsers.UserName as FromUserName
	, case when FromUsers.DisplayName = '' then '<None>' else FromUsers.DisplayName end as FromDisplayName
	, count(distinct ForumMessageVotes.Id) as Upvote
from [dbo].[ForumMessageVotes] as ForumMessageVotes
	inner join [dbo].[Users] as FromUsers
		on FromUsers.Id = ForumMessageVotes.FromUserId

lfy79001

    SELECT 
        namePlayer AS player_name,
        nameTeam AS team_name,
        Player_Attributes.DRAFT_YEAR AS draft_year,
        Player_Attributes.POSITION AS game_position,
        Player_Attributes.PTS AS points,
        Player_Attributes.AST AS assists,
        Player_Attributes.REB AS rebounds,
        ROUND(value/1000000) AS salary_in_millions
    FROM Player_Salary

lfy79001

  SELECT 
      SEASON_ID-20000 AS season,
      TEAM_ID_HOME AS team_id,
      TEAM_NAME_HOME AS team_name,
      FG3_PCT_HOME AS three_point_percentage,
      COUNT(TEAM_ID_HOME) AS team_game_count,
      "home" AS game_location
  FROM Game
  GROUP BY SEASON_ID, TEAM_ID_HOME 

lfy79001

  SELECT 
      namePlayer AS player_name,
      nameTeam AS team_name,
      2021 - strftime('%Y', Player_Attributes.BIRTHDATE) AS age,
      Player_Attributes.DRAFT_YEAR AS draft_year,
      2021 - Player_Attributes.DRAFT_YEAR AS years_in_NBA,
      Player_Attributes.POSITION AS game_position,
      ROUND(value/1000000) AS salary_in_millions
  FROM Player_Salary
  JOIN Player ON

lfy79001

WITH CustomerData AS (
    SELECT
        customer_unique_id,
        customer_zip_code_prefix AS zip_code_prefix,
        COUNT(DISTINCT orders.order_id) AS order_count,
        SUM(payment_value) AS total_payment,
        JULIANDAY(MIN(order_purchase_timestamp)) AS first_order_day,
        JULIANDAY(MAX(order_purchase_timestamp)) AS last_order_day
    FROM customers
        JOIN orders USING (customer_id)

lfy79001

WITH RecencyScore AS (
    SELECT customer_unique_id,
           MAX(order_purchase_timestamp) AS last_purchase,
           NTILE(5) OVER (ORDER BY MAX(order_purchase_timestamp) DESC) AS recency
    FROM orders
        JOIN customers USING (customer_id)
    WHERE order_status = 'delivered'
    GROUP BY customer_unique_id
),
FrequencyScore AS (

lfy79001

WITH DailySalesPerCategory AS (
    SELECT
        DATE(order_purchase_timestamp) AS date,
        -- Days since 2017-01-01
        CAST(JULIANDAY(order_purchase_timestamp) - JULIANDAY('2017-01-01') AS INTEGER) AS day,
        product_category_name_english AS category,
        SUM(price) AS sales
    FROM
        orders
        JOIN order_items USING (order_id)

lfy79001

SELECT
    DATE(order_purchase_timestamp) AS date,
    -- Days since 2017-01-01
    CAST(JULIANDAY(order_purchase_timestamp) - JULIANDAY('2017-01-01') AS INTEGER) AS day,
    product_category_name_english AS category,
    SUM(price) AS sales
FROM
    orders
    JOIN order_items USING (order_id)
    JOIN products USING (product_id)

lfy79001

{{ config(enabled=var('ad_reporting__google_ads_enabled', True)) }}

with base as (

    select * 
    from {{ ref('stg_google_ads__ad_stats_tmp') }}

),

fields as (

lfy79001

{{ config(enabled=var('ad_reporting__google_ads_enabled', True)) }}

with base as (

    select * 
    from {{ ref('stg_google_ads__ad_history_tmp') }}
),

fields as (