import { MetricInterface } from "../../types/metric" ; import { DataSourceSettings , DataSourceProperties , } from "../../types/datasource" ; import { ImpactEstimationResult , MetricValueParams , SourceIntegrationInterface , ExperimentMetricQueryParams , PastExperimentParams , PastExperimentResponse , ExperimentMetricQueryResponse , MetricValueQueryResponse , MetricValueQueryResponseRow , ExperimentQueryResponses , } from "../types/Integration" ; import { format , FormatOptions } from "sql-formatter" ; import { ExperimentPhase , ExperimentInterface } from "../../types/experiment" ; import { DimensionInterface } from "../../types/dimension" ; import { SegmentInterface } from "../../types/segment" ; import { DEFAULT_CONVERSION_WINDOW_HOURS } from "../util/secrets" ; import { processMetricValueQueryResponse } from "../services/queries" ; import { getValidDate } from "../util/dates" ; const percentileNumbers = [ 0.01 , 0.05 , 0.1 , 0.2 , 0.3 , 0.4 , 0.5 , 0.6 , 0.7 , 0.8 , 0.9 , 0.95 , 0.99 , ] ; function replaceDateVars ( sql , startDate , endDate ? ) { if ( ! endDate ) { const now = new Date ( ) ; endDate = new Date ( now . getFullYear ( ) , now . getMonth ( ) , now . getDate ( ) + 2 , 0 , 0 , 0 , 0 ) ; } return sql . replace ( /\{\{\s*startDate\s*\}\}/g , startDate . toISOString ( ) . substr ( 0 , 19 ) . replace ( "T" , " " ) ) . replace ( /\{\{\s*endDate\s*\}\}/g , endDate . toISOString ( ) . substr ( 0 , 19 ) . replace ( "T" , " " ) ) ; } export function getExperimentQuery ( settings , schema ? ) { if ( settings ?. queries ?. experimentsQuery ) { return settings . queries . experimentsQuery ; } return `SELECT
  ${ settings ?. experiments ?. userIdColumn || settings ?. default ?. userIdColumn || "user_id" } as user_id,
  ${ settings ?. experiments ?. anonymousIdColumn || settings ?. default ?. anonymousIdColumn || "anonymous_id" } as anonymous_id,
  ${ settings ?. experiments ?. timestampColumn || settings ?. default ?. timestampColumn || "received_at" } as timestamp,
  ${ settings ?. experiments ?. experimentIdColumn || "experiment_id" } as experiment_id,
  ${ settings ?. experiments ?. variationColumn || "variation_id" } as variation_id
FROM 
  ${ schema && ! settings ?. experiments ?. table ?. match ( /\./ ) ? schema + "." : "" }${ settings ?. experiments ?. table || "experiment_viewed" }` ; } export function getPageviewsQuery ( settings , schema ? ) { if ( settings ?. queries ?. pageviewsQuery ) { return settings . queries . pageviewsQuery ; } return `SELECT
  ${ settings ?. pageviews ?. userIdColumn || settings ?. default ?. userIdColumn || "user_id" } as user_id,
  ${ settings ?. pageviews ?. anonymousIdColumn || settings ?. default ?. anonymousIdColumn || "anonymous_id" } as anonymous_id,
  ${ settings ?. pageviews ?. timestampColumn || settings ?. default ?. timestampColumn || "received_at" } as timestamp,
  ${ settings ?. pageviews ?. urlColumn || "path" } as url
FROM 
  ${ schema && ! settings ?. pageviews ?. table ?. match ( /\./ ) ? schema + "." : "" }${ settings ?. pageviews ?. table || "pages" }` ; } export default abstract class SqlIntegration implements SourceIntegrationInterface { settings : DataSourceSettings ; datasource : string ; organization : string ; params : any ; abstract setParams ( encryptedParams ) ; abstract runQuery ( sql ) ; abstract percentile ( col , percentile ) ; abstract getSensitiveParamKeys ( ) ; constructor ( encryptedParams , settings ) { this . setParams ( encryptedParams ) ; this . settings = { ... settings , } ; } getSourceProperties ( ) { return { queryLanguage : "sql" , metricCaps : true , segments : true , dimensions : true , separateExperimentResultQueries : true , hasSettings : true , userIds : true , experimentSegments : true , activationDimension : true , pastExperiments : true , } ; } async testConnection ( ) { await this . runQuery ( "select 1" ) ; return true ; } getSchema ( ) { return "" ; } toTimestamp ( date ) { return `'${ date . toISOString ( ) . substr ( 0 , 19 ) . replace ( "T" , " " ) }'` ; } addHours ( col , hours ) { return `${ col } + INTERVAL '${ hours } hours'` ; } subtractHalfHour ( col ) { return `${ col } - INTERVAL '30 minutes'` ; } regexMatch ( col , regex ) { return `${ col } ~ '${ regex }'` ; } dateTrunc ( col ) { return `date_trunc('day', ${ col })` ; } dateDiff ( startCol , endCol ) { return `datediff(day, ${ startCol }, ${ endCol })` ; } convertDate ( fromDB ) { return getValidDate ( fromDB ) ; } stddev ( col ) { return `STDDEV(${ col })` ; } avg ( col ) { return `AVG(${ col })` ; } formatDate ( col ) { return col ; } ifElse ( condition , ifTrue , ifFalse ) { return `(CASE WHEN ${ condition } THEN ${ ifTrue } ELSE ${ ifFalse } END)` ; } castToString ( col ) { return `cast(${ col } as varchar)` ; } getPastExperimentQuery ( params ) { const minLength = params . minLength ?? 6 ; const now = new Date ( ) ; return format ( `-- Past Experiments
    WITH
      __experiments as (
        ${ replaceDateVars ( getExperimentQuery ( this . settings , this . getSchema ( ) ) , params . from ) }
      ),
      __experimentDates as (
        SELECT
          experiment_id,
          variation_id,
          ${ this . dateTrunc ( "timestamp" ) } as date,
          count(distinct anonymous_id) as users
        FROM
          __experiments
        WHERE
          timestamp > ${ this . toTimestamp ( params . from ) }
        GROUP BY
          experiment_id,
          variation_id,
          ${ this . dateTrunc ( "timestamp" ) }
      ),
      __userThresholds as (
        SELECT
          experiment_id,
          variation_id,
          -- It's common for a small number of tracking events to continue coming in
          -- long after an experiment ends, so limit to days with enough traffic
          max(users)*0.05 as threshold
        FROM
          __experimentDates
        WHERE
          -- Skip days where a variation got 5 or fewer visitors since it's probably not real traffic
          users > 5
        GROUP BY
          experiment_id, variation_id
      ),
      __variations as (
        SELECT
          d.experiment_id,
          d.variation_id,
          MIN(d.date) as start_date,
          MAX(d.date) as end_date,
          SUM(d.users) as users
        FROM
          __experimentDates d
          JOIN __userThresholds u ON (
            d.experiment_id = u.experiment_id
            AND d.variation_id = u.variation_id
          )
        WHERE
          d.users > u.threshold
        GROUP BY
          d.experiment_id, d.variation_id
      )
    SELECT
      *
    FROM
      __variations
    WHERE
      -- Experiment was started recently
      ${ this . dateDiff ( "start_date" , this . toTimestamp ( now ) ) } < ${ minLength } OR
      -- OR it ran for long enough and had enough users
      (
        ${ this . dateDiff ( "start_date" , "end_date" ) } >= ${ minLength } AND
        users > 100 AND
        -- Skip experiments at start of date range since it's likely missing data
        ${ this . dateDiff ( this . toTimestamp ( params . from ) , "start_date" ) } > 2
      )
    ORDER BY
      experiment_id ASC, variation_id ASC` , this . getFormatOptions ( ) ) ; } async runPastExperimentQuery ( query ) { const rows = await this . runQuery ( query ) ; return rows . map ( ( row ) => { return { experiment_id : row . experiment_id , variation_id : row . variation_id ?? "" , users : parseInt ( row . users ) || 0 , end_date : this . convertDate ( row . end_date ) . toISOString ( ) , start_date : this . convertDate ( row . start_date ) . toISOString ( ) , } ; } ) ; } getMetricValueQuery ( params ) { const userId = params . userIdType === "user" ; const metricStart = new Date ( params . from ) ; metricStart . setMinutes ( metricStart . getMinutes ( ) - 30 ) ; const metricEnd = new Date ( params . to ) ; metricEnd . setHours ( metricEnd . getHours ( ) + ( params . metric . conversionWindowHours || DEFAULT_CONVERSION_WINDOW_HOURS ) ) ; return format ( `-- ${ params . name } - ${ params . metric . name } Metric
      WITH
        ${ this . getIdentifiesCTE ( userId , { from : params . from , to : params . to , segment : ! ! params . segmentQuery , metrics : [ params . metric ] , } ) }
        __pageviews as (${ replaceDateVars ( getPageviewsQuery ( this . settings , this . getSchema ( ) ) , params . from , params . to ) }),
        __users as (${ this . getPageUsersCTE ( params , userId , params . metric . conversionWindowHours ) })
        ${ params . segmentQuery ? `, segment as (${ this . getSegmentCTE ( params . segmentQuery , params . segmentName || "" , userId ) })` : "" }
        , __metric as (${ this . getMetricCTE ( { metric : params . metric , userId , startDate : metricStart , endDate : metricEnd , } ) })
        , __distinctUsers as (
          SELECT
            u.user_id,
            MIN(u.conversion_end) as conversion_end,
            MIN(u.session_start) as session_start,
            MIN(u.actual_start) as actual_start
          FROM
            __users u
            ${ params . segmentQuery ? "JOIN segment s ON (s.user_id = u.user_id) WHERE s.date <= u.actual_start" : "" }
          GROUP BY
            u.user_id
        )
        , __userMetric as (
          -- Add in the aggregate metric value for each user
          SELECT
            ${ this . getAggregateMetricSqlValue ( params . metric ) } as value
          FROM
            __distinctUsers d
            JOIN __metric m ON (
              m.user_id = d.user_id
            )
            WHERE
              m.actual_start >= d.${ params . metric . earlyStart ? "session_start" : "actual_start" }
              AND m.actual_start <= d.conversion_end
          GROUP BY
            d.user_id
        )
        , __overallUsers as (
          -- Number of users overall
          SELECT
            COUNT(*) as users
          FROM
            __distinctUsers
        )
        , __overall as (
          SELECT
            COUNT(*) as count,
            ${ this . avg ( "value" ) } as mean,
            ${ this . stddev ( "value" ) } as stddev
            ${ params . includePercentiles && params . metric . type !== "binomial" ? `,${ percentileNumbers . map ( ( n ) => `${ this . percentile ( "value" , n ) } as p${ Math . floor ( n * 100 ) }` ) . join ( "\n      ," ) }` : "" }
          from
            __userMetric
        )
        ${ params . includeByDate ? `
          , __userMetricDates as (
            -- Add in the aggregate metric value for each user
            SELECT
              ${ this . dateTrunc ( "d.actual_start" ) } as date,
              ${ this . getAggregateMetricSqlValue ( params . metric ) } as value
            FROM
              __distinctUsers d
              JOIN __metric m ON (
                m.user_id = d.user_id
              )
              WHERE
                m.actual_start >= d.${ params . metric . earlyStart ? "session_start" : "actual_start" }
                AND m.actual_start <= d.conversion_end
            GROUP BY
              ${ this . dateTrunc ( "d.actual_start" ) },
              d.user_id
          )
          , __usersByDate as (
            -- Number of users by date
            SELECT
              ${ this . dateTrunc ( "actual_start" ) } as date,
              COUNT(*) as users
            FROM
              __distinctUsers
            GROUP BY
              ${ this . dateTrunc ( "actual_start" ) }
          )
          , __byDateOverall as (
            SELECT
              date,
              COUNT(*) as count,
              ${ this . avg ( "value" ) } as mean,
              ${ this . stddev ( "value" ) } as stddev
              ${ params . includePercentiles && params . metric . type !== "binomial" ? `,${ percentileNumbers . map ( ( n ) => `0 as p${ Math . floor ( n * 100 ) }` ) . join ( "\n      ," ) }` : "" }
            FROM
              __userMetricDates d
            GROUP BY
              date
          )` : "" }
      SELECT
        ${ params . includeByDate ? "null as date," : "" }
        o.*,
        u.users
      FROM
        __overall o
        JOIN __overallUsers u ON (1=1)
      ${ params . includeByDate ? `
        UNION ALL SELECT
          o.*,
          u.users
        FROM
          __byDateOverall o
          JOIN __usersByDate u ON (o.date = u.date)
        ORDER BY
          date ASC
      ` : "" }
      ` , this . getFormatOptions ( ) ) ; } async runExperimentMetricQuery ( query ) { const rows = await this . runQuery ( query ) ; return rows . map ( ( row ) => { return { variation : row . variation ?? "" , dimension : row . dimension || "" , users : parseInt ( row . users ) || 0 , count : parseFloat ( row . count ) || 0 , mean : parseFloat ( row . mean ) || 0 , stddev : parseFloat ( row . stddev ) || 0 , } ; } ) ; } async runMetricValueQuery ( query ) { const rows = await this . runQuery ( query ) ; return rows . map ( ( row ) => { const { date , count , mean , users , stddev , ... percentiles } = row ; const ret = { date : date ? this . convertDate ( date ) . toISOString ( ) : "" , users : parseInt ( users ) || 0 , count : parseFloat ( count ) || 0 , mean : parseFloat ( mean ) || 0 , stddev : parseFloat ( stddev ) || 0 , } ; if ( percentiles ) { Object . keys ( percentiles ) . forEach ( ( p ) => { ret [ p ] = parseFloat ( percentiles [ p ] ) || 0 ; } ) ; } return ret ; } ) ; } getFormatOptions ( ) { return { language : "redshift" , } ; } async getImpactEstimation ( urlRegex , metric , segment ? ) { const numDays = 30 ; const conversionWindowHours = metric . conversionWindowHours || DEFAULT_CONVERSION_WINDOW_HOURS ; const end = new Date ( ) ; end . setHours ( end . getHours ( ) - conversionWindowHours ) ; const start = new Date ( ) ; start . setDate ( start . getDate ( ) - numDays ) ; start . setHours ( start . getHours ( ) - conversionWindowHours ) ; const baseSettings = { from : start , to : end , includeByDate : false , userIdType : metric . userIdType || "either" , conversionWindowHours , } ; const metricSql = this . getMetricValueQuery ( { ... baseSettings , name : "Metric Value - Entire Site" , metric , includePercentiles : false , } ) ; const valueSql = this . getMetricValueQuery ( { ... baseSettings , name : "Metric Value - Selected Pages and Segment" , metric , includePercentiles : false , urlRegex , segmentQuery : segment ?. sql , segmentName : segment ?. name , } ) ; const [ metricTotalResponse , valueResponse ] = await Promise . all ( [ this . runMetricValueQuery ( metricSql ) , this . runMetricValueQuery ( valueSql ) , ] ) ; const metricTotal = processMetricValueQueryResponse ( metricTotalResponse ) ; const value = processMetricValueQueryResponse ( valueResponse ) ; const formatted = [ metricSql , valueSql ] . map ( ( sql ) => format ( sql , this . getFormatOptions ( ) ) ) . join ( ";\n\n" ) + ";" ; return { query : formatted , users : value . users , value : ( value . count * value . mean ) / numDays , metricTotal : ( metricTotal . count * metricTotal . mean ) / numDays , } ; } private getIdentifiesCTE ( userId , { from , to , metrics , dimension , segment , } ) { const select = `__identities as (
      SELECT
        user_id,
        anonymous_id
      FROM
        (${ replaceDateVars ( getPageviewsQuery ( this . settings , this . getSchema ( ) ) , from , to ) }) i
      WHERE
        i.timestamp >= ${ this . toTimestamp ( from ) }
        ${ to ? `AND i.timestamp <= ${ this . toTimestamp ( to ) }` : "" }
      GROUP BY
        user_id, 
        anonymous_id
    ),` ; if ( metrics ) { for ( let i = 0 ; i < metrics . length ; i ++ ) { if ( ! metrics [ i ] ) continue ; if ( userId && metrics [ i ] ?. userIdType === "anonymous" ) { return select ; } else if ( ! userId && metrics [ i ] ?. userIdType === "user" ) { return select ; } } } if ( dimension && ! userId ) return select ; if ( segment && ! userId ) return select ; return "" ; } private getIdentifiesJoinSql ( column , userId = true ) { return `JOIN __identities i ON (
      i.${ userId ? "user_id" : "anonymous_id" } = ${ column }
    )` ; } private getActivatedUsersCTE ( ) { return `
      SELECT
        e.user_id,
        a.actual_start,
        a.session_start,
        a.conversion_end
      FROM
        __experiment e
        JOIN __activationMetric a ON (
          a.user_id = e.user_id
        )
      WHERE
        a.actual_start >= e.actual_start
        AND a.actual_start <= e.conversion_end` ; } private ifNullFallback ( nullable , fallback ) { if ( ! nullable ) return fallback ; return `COALESCE(${ nullable }, ${ fallback })` ; } getExperimentMetricQuery ( params ) { const { metric , experiment , phase , dimension , activationMetric , segment , } = params ; const userId = experiment . userIdType === "user" ; const activationDimension = activationMetric && dimension ?. type === "activation" ; const metricStart = new Date ( phase . dateStarted ) ; metricStart . setMinutes ( metricStart . getMinutes ( ) - 30 ) ; const metricEnd = phase . dateEnded ? new Date ( phase . dateEnded ) : null ; if ( metricEnd ) { metricEnd . setHours ( metricEnd . getHours ( ) + ( metric . conversionWindowHours || DEFAULT_CONVERSION_WINDOW_HOURS ) + ( activationMetric ? activationMetric . conversionWindowHours || DEFAULT_CONVERSION_WINDOW_HOURS : 0 ) ) ; } const removeMultipleExposures = ! ! experiment . removeMultipleExposures ; return format ( `-- ${ metric . name } (${ metric . type })
    WITH
      ${ this . getIdentifiesCTE ( userId , { from : phase . dateStarted , to : phase . dateEnded , dimension : dimension ?. type === "user" , segment : ! ! segment , metrics : [ metric , activationMetric ] , } ) }
      __rawExperiment as (${ replaceDateVars ( getExperimentQuery ( this . settings , this . getSchema ( ) ) , phase . dateStarted , phase . dateEnded ) }),
      __experiment as (${ this . getExperimentCTE ( { experiment , phase , conversionWindowHours : ( activationMetric ? activationMetric . conversionWindowHours : metric . conversionWindowHours ) || 0 , experimentDimension : dimension ?. type === "experiment" ? dimension . id : null , } ) })
      , __metric as (${ this . getMetricCTE ( { metric , userId , startDate : metricStart , endDate : metricEnd , } ) })
      ${ segment ? `, __segment as (${ this . getSegmentCTE ( segment . sql , segment . name , userId ) })` : "" }
      ${ dimension ?. type === "user" ? `, __dimension as (${ this . getDimensionCTE ( dimension . dimension , userId ) })` : "" }
      ${ activationMetric ? `, __activationMetric as (${ this . getMetricCTE ( { metric : activationMetric , conversionWindowHours : metric . conversionWindowHours , userId , startDate : metricStart , endDate : metricEnd , } ) })
            , __activatedUsers as (${ this . getActivatedUsersCTE ( ) })` : "" }
      , __distinctUsers as (
        -- One row per user/dimension${ removeMultipleExposures ? "" : "/variation" }
        SELECT
          e.user_id,
          ${ dimension ?. type === "user" ? "d.value" : dimension ?. type === "experiment" ? "e.dimension" : dimension ?. type === "date" ? this . formatDate ( this . dateTrunc ( "e.actual_start" ) ) : activationDimension ? this . ifElse ( "a.user_id IS NULL" , "'Not Activated'" , "'Activated'" ) : "'All'" } as dimension,
          ${ removeMultipleExposures ? this . ifElse ( "count(distinct e.variation) > 1" , "'__multiple__'" , "max(e.variation)" ) : "e.variation" } as variation,
          MIN(${ this . ifNullFallback ( activationMetric ? "a.actual_start" : null , "e.actual_start" ) }) as actual_start,
          MIN(${ this . ifNullFallback ( activationMetric ? "a.session_start" : null , "e.session_start" ) }) as session_start,
          MIN(${ this . ifNullFallback ( activationMetric ? "a.conversion_end" : null , "e.conversion_end" ) }) as conversion_end
        FROM
          __experiment e
          ${ segment ? "JOIN __segment s ON (s.user_id = e.user_id)" : "" }
          ${ dimension ?. type === "user" ? "JOIN __dimension d ON (d.user_id = e.user_id)" : "" }
          ${ activationMetric ? `
          ${ activationDimension ? "LEFT " : "" }JOIN __activatedUsers a ON (
            a.user_id = e.user_id
          )` : "" }
        ${ segment ? `WHERE s.date <= e.actual_start` : "" }
        GROUP BY
          dimension, e.user_id${ removeMultipleExposures ? "" : ", e.variation" }
      )
      , __userMetric as (
        -- Add in the aggregate metric value for each user
        SELECT
          d.variation,
          d.dimension,
          ${ this . getAggregateMetricSqlValue ( metric ) } as value
        FROM
          __distinctUsers d
          JOIN __metric m ON (
            m.user_id = d.user_id
          )
          WHERE
            m.actual_start >= d.${ metric . earlyStart ? "session_start" : "actual_start" }
            AND m.actual_start <= d.conversion_end
        GROUP BY
          variation, dimension, d.user_id
      )
      , __overallUsers as (
        -- Number of users in each variation
        SELECT
          variation,
          dimension,
          COUNT(*) as users
        FROM
          __distinctUsers
        GROUP BY
          variation,
          dimension
      )
      , __stats as (    
        -- Sum all user metrics together to get a total per variation/dimension
        SELECT
          variation,
          dimension,
          COUNT(*) as count,
          ${ this . avg ( "value" ) } as mean,
          ${ this . stddev ( "value" ) } as stddev
        FROM
          __userMetric
        GROUP BY
          variation,
          dimension
      )
    SELECT
      s.variation,
      s.dimension,
      s.count,
      s.mean,
      s.stddev,
      u.users
    FROM
      __stats s
      JOIN __overallUsers u ON (
        s.variation = u.variation 
        AND s.dimension = u.dimension
      )
    ` , this . getFormatOptions ( ) ) ; } getExperimentResultsQuery ( ) { throw new Error ( "Not implemented" ) ; } async getExperimentResults ( ) { throw new Error ( "Not implemented" ) ; } private getMetricCTE ( { metric , conversionWindowHours = DEFAULT_CONVERSION_WINDOW_HOURS , userId = true , startDate , endDate , } ) { let userIdCol ; let join = "" ; if ( userId && metric . userIdType === "anonymous" ) { userIdCol = "i.user_id" ; join = this . getIdentifiesJoinSql ( "m." + this . getAnonymousIdColumn ( metric ) , false ) ; } else if ( ! userId && metric . userIdType === "user" ) { userIdCol = "i.anonymous_id" ; join = this . getIdentifiesJoinSql ( "m." + this . getUserIdColumn ( metric ) , true ) ; } else { userIdCol = "m." + ( userId ? this . getUserIdColumn ( metric ) : this . getAnonymousIdColumn ( metric ) ) ; } const timestampCol = "m." + this . getTimestampColumn ( metric ) ; const schema = this . getSchema ( ) ; const where = [ ] ; if ( metric . conditions ?. length ) { metric . conditions . forEach ( ( c ) => { where . push ( `m.${ c . column } ${ c . operator } '${ c . value }'` ) ; } ) ; } if ( startDate ) { where . push ( `${ timestampCol } >= ${ this . toTimestamp ( startDate ) }` ) ; } if ( endDate ) { where . push ( `${ timestampCol } <= ${ this . toTimestamp ( endDate ) }` ) ; } return `-- Metric (${ metric . name })
      SELECT
        ${ userIdCol } as user_id,
        ${ this . getRawMetricSqlValue ( metric , "m" ) } as value,
        ${ timestampCol } as actual_start,
        ${ this . addHours ( timestampCol , conversionWindowHours ) } as conversion_end,
        ${ this . subtractHalfHour ( timestampCol ) } as session_start
      FROM
        ${ metric . sql ? `(${ replaceDateVars ( metric . sql , startDate , endDate || undefined ) })` : ( schema && ! metric . table ?. match ( /\./ ) ? schema + "." : "" ) + ( metric . table || "" ) } m
        ${ join }
        ${ where . length ? `WHERE ${ where . join ( " AND " ) }` : "" }
    ` ; } private getExperimentEndDate ( experiment , phase , conversionWindowHours ) { if ( experiment . skipPartialData ) { const conversionWindowEndDate = new Date ( ) ; conversionWindowEndDate . setHours ( conversionWindowEndDate . getHours ( ) - conversionWindowHours ) ; return new Date ( Math . min ( phase ?. dateEnded ?. getTime ( ) ?? Date . now ( ) , conversionWindowEndDate . getTime ( ) ) ) ; } else if ( phase . dateEnded ) { return phase . dateEnded ; } return null ; } private getExperimentCTE ( { experiment , phase , conversionWindowHours = 0 , experimentDimension = null , } ) { conversionWindowHours = conversionWindowHours || DEFAULT_CONVERSION_WINDOW_HOURS ; const userIdCol = experiment . userIdType === "user" ? "e.user_id" : "e.anonymous_id" ; const endDate = this . getExperimentEndDate ( experiment , phase , conversionWindowHours ) ; return `-- Viewed Experiment
    SELECT
      ${ userIdCol } as user_id,
      ${ this . castToString ( "e.variation_id" ) } as variation,
      e.timestamp as actual_start,
      ${ experimentDimension ? `e.${ experimentDimension } as dimension,` : "" }
      ${ this . addHours ( "e.timestamp" , conversionWindowHours ) } as conversion_end,
      ${ this . subtractHalfHour ( "e.timestamp" ) } as session_start
    FROM
        __rawExperiment e
    WHERE
        e.experiment_id = '${ experiment . trackingKey }'
        AND e.timestamp >= ${ this . toTimestamp ( phase . dateStarted ) }
        ${ endDate ? `AND e.timestamp <= ${ this . toTimestamp ( endDate ) }` : "" }
        ${ experiment . queryFilter ? `AND (${ experiment . queryFilter })` : "" }
    ` ; } private getSegmentCTE ( sql , name , userId = true ) { if ( ! userId ) { return `-- Segment (${ name })
      SELECT
        i.anonymous_id as user_id,
        s.date
      FROM
        (${ sql }) s
        ${ this . getIdentifiesJoinSql ( "s.user_id" , true ) }
      ` ; } return `-- Segment (${ name })
    ${ sql }
    ` ; } private getDimensionCTE ( dimension , userId = true ) { if ( ! userId ) { return `-- Dimension (${ dimension . name })
      SELECT
        i.anonymous_id as user_id,
        d.value
      FROM
        (${ dimension . sql }) d
        ${ this . getIdentifiesJoinSql ( "d.user_id" , true ) }
      ` ; } return `-- Dimension (${ dimension . name })
    ${ dimension . sql }
    ` ; } private getPageUsersCTE ( params , userId = true , conversionWindowHours = DEFAULT_CONVERSION_WINDOW_HOURS ) { const userIdCol = userId ? "p.user_id" : "p.anonymous_id" ; return `-- Users visiting specific pages
    SELECT
      ${ userIdCol } as user_id,
      MIN(p.timestamp) as actual_start,
      ${ this . addHours ( `MIN(p.timestamp)` , conversionWindowHours ) } as conversion_end,
      ${ this . subtractHalfHour ( `MIN(p.timestamp)` ) } as session_start
    FROM
        __pageviews p
    WHERE
      p.timestamp >= ${ this . toTimestamp ( this . dateOnly ( params . from ) ) }
      AND p.timestamp <= ${ this . toTimestamp ( this . dateOnly ( params . to ) ) }
      ${ params . urlRegex && params . urlRegex !== ".*" ? `AND ${ this . regexMatch ( "p.url" , params . urlRegex ) }` : "" }
    GROUP BY
      ${ userIdCol }
    ` ; } private dateOnly ( orig ) { const date = new Date ( orig ) ; date . setHours ( 0 ) ; date . setMinutes ( 0 ) ; date . setSeconds ( 0 ) ; date . setMilliseconds ( 0 ) ; return date ; } private capValue ( cap , value ) { if ( ! cap ) { return value ; } return `LEAST(${ cap }, ${ value })` ; } private getMetricColumn ( metric , alias = "m" ) { if ( metric . sql ) return alias + ".value" ; if ( metric . type === "duration" ) { if ( metric . column ?. match ( /\{alias\}/ ) ) { return metric . column . replace ( /\{alias\}/g , alias ) ; } } return alias + "." + metric . column ; } private getRawMetricSqlValue ( metric , alias = "m" ) { if ( metric . type === "binomial" ) { return "1" ; } else if ( metric . sql ) { return alias + ".value" ; } else if ( metric . type === "count" ) { return metric . column ? this . getMetricColumn ( metric , alias ) : "1" ; } else if ( metric . type === "duration" ) { return this . getMetricColumn ( metric , alias ) ; } else if ( metric . type === "revenue" ) { return this . getMetricColumn ( metric , alias ) ; } return "1" ; } private getAggregateMetricSqlValue ( metric ) { if ( metric . type === "binomial" ) { return "1" ; } if ( metric . aggregation ) { return this . capValue ( metric . cap , metric . aggregation ) ; } if ( metric . type === "count" ) { return this . capValue ( metric . cap , metric . sql ? `SUM(value)` : `COUNT(${ metric . column ? `DISTINCT value` : "*" })` ) ; } else if ( metric . type === "duration" ) { return this . capValue ( metric . cap , metric . sql ? `SUM(value)` : `MAX(value)` ) ; } else if ( metric . type === "revenue" ) { return this . capValue ( metric . cap , metric . sql ? `SUM(value)` : `MAX(value)` ) ; } return "1" ; } private getUserIdColumn ( metric ) { return metric . sql ? "user_id" : metric . userIdColumn || "user_id" ; } private getAnonymousIdColumn ( metric ) { return metric . sql ? "anonymous_id" : metric . anonymousIdColumn || "anonymous_id" ; } private getTimestampColumn ( metric ) { return metric . sql ? "timestamp" : metric . timestampColumn || "received_at" ; } }