lfy79001

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

with stats as (

    select *
    from {{ var('keyword_stats') }}
), 

accounts as (

lfy79001

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

with stats as (

    select *
    from {{ var('campaign_stats') }}
), 

accounts as (

lfy79001

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

with stats as (

    select *
    from {{ var('ad_stats') }}
), 

accounts as (

lfy79001

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

with stats as (

    select *
    from {{ var('ad_group_stats') }}
), 

accounts as (

lfy79001

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

with stats as (

    select *
    from {{ var('account_stats') }}
), 

accounts as (

lfy79001

with enrollments_select as (
    select
        year,
        unitid,
        unitid_name,
        enrollments unitid_enrollments_total
    from
        CORELMI_US_V2.DAT_ENROLLMENTS_SAMPLE
    where
        raceid = '0'

lfy79001

WITH DATES AS
(
    SELECT
        day_num,

        -- This will get the forecast dates for the next week (week is Sun. to Sat.

        DATEADD(day,day_num - 8, LAST_DAY(DATEADD(week,1,CURRENT_DATE()),week)) AS forecast_date_valid_std,

        DATEADD(day,DAYOFYEAR(forecast_date_valid_std) - 1,DATE_TRUNC(year,DATEADD(year,-1,forecast_date_valid_std))) AS history_date_valid_std

lfy79001

WITH bicycle_rentals AS (
  SELECT
    COUNT(starttime) as num_trips,
    EXTRACT(DATE from starttime) as trip_date
 FROM `bigquery-public-data.new_york_citibike.citibike_trips`
 GROUP BY trip_date
),

rainy_days AS
(

lfy79001

DECLARE
  my_drug_list ARRAY<STRING>;
SET
  my_drug_list = [ 'Premarin',
  'Calcium disodium versenate',
  'Keytruda',
  'Vioxx',
  'Humira' ];
SELECT
  id AS drug_id,

lfy79001

SELECT
  associations.targetId AS target_id,
  targets.approvedSymbol AS target_approved_symbol,
  associations.diseaseId AS disease_id,
  diseases.name AS disease_name,
  associations.score AS overall_association_score
FROM
  `open-targets-prod.platform.associationByOverallDirect` AS associations
JOIN
  `open-targets-prod.platform.diseases` AS diseases