egress charges query

egress_01.sql

SELECT
  dateString,
  dayOfYear,
  week,
  year,
  projectID,
  productCat,
  resourceType,
  SUM(cost) AS totCost,
  currency,
  SUM(usageAmount) AS totUsage,
  unit
FROM (
  SELECT
    NTH(1, SPLIT(STRING(endTime),' ')) AS dateString,
    DAYOFYEAR(endTime) AS dayOfYear,
    WEEK(endTime) AS week,
    YEAR(endTime) AS year,
    projectID,
    productCat,
    resourceType,
    cost,
    currency,
    usageAmount,
    unit
  FROM (
    SELECT
      billing_account_id AS billingAccountID,
      product AS productCat,
      resource_type AS resourceType,
      end_time AS endTime,
      project.id AS projectID,
      project.name AS projectName,
      cost AS cost,
      currency AS currency,
      usage.amount AS usageAmount,
      usage.unit AS unit
    FROM
      [isb-cgc:billing_new.gcp_billing_export_00D005_77247F_DF2BF1] )
  WHERE
    (productCat="Compute Engine")
    AND (resourceType CONTAINS "Egress") )
WHERE
  year=2017
  AND WEEK>31
  AND (projectID="isb-cgc-test")
GROUP BY
  dateString,
  dayOfYear,
  week,
  year,
  projectID,
  productCat,
  resourceType,
  currency,
  unit
HAVING
  (totCost IS NOT NULL)
  AND (totUsage>=100000)
ORDER BY
  dateString DESC,
  totUsage DESC