cube('insights', {
  sql: `
        SELECT
            ingest_impressions.created,
            ingest_impressions.owner,
            ingest_impressions.impressionid,
            ingest_impressions.uuid,
            ingest_impressions.url,
            ingest_impressions.trafficsource,
            CAST(ingest_impressions.earned AS INT) as earned_int,
            ingest_impressions.device,
            ingest_impressions.city,
            ingest_impressions.country,
            MAX(ingest_impressions.isimpression) AS impressions,
            SUM(ingest_impressions.timespent) AS time_spent,
            SUM(ingest_impressions.scrolled) AS scrolled
        FROM
            ingest_impressions
        GROUP BY
            ingest_impressions.created,
            ingest_impressions.owner,
            ingest_impressions.impressionid,
            ingest_impressions.uuid,
            ingest_impressions.url,
            ingest_impressions.trafficsource,
            ingest_impressions.earned,
            ingest_impressions.device,
            ingest_impressions.city,
            ingest_impressions.country
      `,
    measures: {
        impressions: {
            sql: `impressions`,
            type: `sum`
        },
        people: {
            sql: `count_distinct(uuid)`,
            type: `number`
        },
        averageAttention: {
            sql: `CASE WHEN impressions > 0 THEN time_spent / impressions ELSE 0 END`,
            type: `avg`
        },
        totalAttention: {
            sql: `CASE WHEN impressions > 0 THEN time_spent / impressions ELSE 0 END`,
            type: `sum`
        },
        averageScrolled: {
            sql: `CASE WHEN impressions > 0 THEN scrolled / impressions ELSE 0 END`,
            type: `avg`
        },
        totalScrolled: {
            sql: `CASE WHEN impressions > 0 THEN scrolled / impressions ELSE 0 END`,
            type: `sum`
        },
        bounced: {
            sql: `CASE WHEN time_spent <= 5 THEN 1 ELSE 0 END`,
            type: `sum`
        },
        //Must include :: float4 to to get bounce rates
        bounceRate: {
            sql: `case
                    when 
                        count(impressionid) > 0 then 
                            sum(case
                                when time_spent <= 5 then 1 :: float4
                                else 0 :: float4
                            end) / count(impressionid) :: float4
                    else 0 :: float4
                        end * 100`,
            type: `string`
        //format: `percent`
        },
        earnedImpressions: {
            sql: `earned_int`,
            type: `sum`
        },
        content_overlap: {
        sql: `
                SELECT COUNT(*) FROM (
                    (SELECT uuid, COUNT(url) as total_urls
                    FROM (SELECT uuid, url FROM ${CUBE} GROUP BY uuid, url)
                    GROUP BY uuid) WHERE total_urls >= 2
                )
                `,
            type: 'number'
        }
    },
    dimensions: {
        impressionid: {
            sql: `${CUBE}.impressionid`,
            type: `number`,
            primaryKey: true
        },
        uuid: {
            sql: `${CUBE}.uuid`,
            type: `string`
        },
        url: {
            sql: `url`,
            type: `string`
        },
        owner: {
            sql: `owner`,
            type: `string`
        },
        trafficsource: {
            sql: `trafficsource`,
            type: `string`
        },
        earned: {
            sql: `earned_int`,
            type: `boolean`
        },
        device: {
            sql: `device`,
            type: `string`
        },
        country: {
            sql: `country`,
            type: `string`
        },
        created: {
            sql: `created`,
            type: `time`
        },
        dayOfWeek: {
            sql: `(${CUBE}.created / 1000 / 60 / 60 / 24 + 5) % 7`,
            type: `number`
        },
        timeOfDay: {
            sql: `(${CUBE}.created / 1000 / 60 / 60) % 24`,
            type: `number`
        }
    },
    joins: {
        time_to_scroll: {
            sql: `${CUBE}.impressionid = ${time_to_scroll}.impressionid`,
            relationship: `belongsTo`
    }
},
preAggregations: {
    mastheadMetrics: {
        measures: [insights.averageAttention, insights.averageScrolled, insights.earnedImpressions, insights.impressions],
        dimensions: [insights.owner],
        timeDimension: insights.created,
        granularity: `day`
    },
    topDevices: {
        measures: [insights.earnedImpressions, insights.people, insights.averageAttention],
        dimensions: [insights.device, insights.owner, insights.url],
        timeDimension: insights.created,
        granularity: `day`
    },
    mostViral: {
        measures: [insights.people, insights.earnedImpressions, insights.averageAttention],
        dimensions: [insights.owner, insights.url],
        refreshKey: {
            every: `1 hour`
        },
        indexes: {
            indexName: {
            columns: [insights.owner, insights.url]
            }
        },
        timeDimension: insights.created,
        granularity: `day`
    },
    bestTimeOfDay: {
      measures: [insights.impressions, insights.averageAttention],
      dimensions: [insights.owner, insights.timeOfDay],
      timeDimension: insights.created,
      granularity: `day`
    },
    bestDayOfWeek: {
      measures: [insights.impressions, insights.totalAttention],
      dimensions: [insights.dayOfWeek, insights.owner],
      timeDimension: insights.created,
      granularity: `day`
    },
    topAverageAttentionUrl: {
      measures: [insights.totalAttention, insights.impressions],
      dimensions: [insights.owner, insights.url]
    },
    topAverageAttentionDevice: {
      measures: [insights.totalAttention, insights.impressions],
      dimensions: [insights.device, insights.owner]
    },
    ExecAverageAttention: {
      measures: [insights.totalAttention, insights.impressions],
      dimensions: [insights.owner]
    },
    ExecTopDevice: {
      measures: [insights.totalAttention, insights.impressions, insights.totalScrolled],
      dimensions: [insights.device, insights.owner]
    },
    ExecBestTrafficSource: {
      measures: [insights.impressions, insights.bounceRate],
      dimensions: [insights.owner, insights.trafficsource]
    },
    ExecTopSocialNetwork: {
      measures: [insights.people, insights.bounceRate],
      dimensions: [insights.owner, insights.trafficsource]
    },
    ExecTopSocialNetworkTotalPeople: {
      measures: [insights.people],
      dimensions: [insights.owner]
    },
    ExecDeviceDistribution: {
      measures: [insights.impressions],
      dimensions: [insights.device, insights.owner]
    }
  }
});