CODEWITHSUNDEEP
sqloptimizationgoogle-bigquery
Arnau Amargant
Arnau Amargant

Reputation: 37

Optimize SQL query for BigQuery

I am trying to optimize and simplify the following SQL query:

WITH details_cost_credit_cud_corrected AS (
    SELECT
      DATE(DATETIME(usage_start_time, "America/Los_Angeles")) as usage_start_time,
      project,
      cost,
      discounts,
      promotions
    FROM
      `ama.details_cost_credit`
  )
  SELECT
    usage_start_time as date,
    IF (project.name IS NULL,"support",project.name) as project_name,
    SUM(cost) as cost_before_credits,
    SUM(cost)+SUM(discounts)+SUM(promotions) as cost_after_credit,
    SUM(cost)+SUM(discounts) as cost_after_discount
  FROM
    details_cost_credit_cud_corrected
  WHERE
    usage_start_time = DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
  AND
    (
      project.name IS NULL
    OR
      project.name IN ("project1","project2","project3")
    )
  GROUP BY usage_start_time, project.name

So basically what this query is doing is creating the "details_cost_credit_cud_corrected" table only changing column "usage_start_time" timezone adding some variables and then it uses this table to filter some more data in the following query, I was trying to see if I could avoid creating the first table but Im having issues parsing the timezone for "usage_start_time" and convert it as a Date at the same time with only one table.

Any help would be appreciated. Thanks in advance.

Upvotes: 0

Views: 57

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Try below

SELECT
  usage_start_time as date,
  IF (project.name IS NULL,"support",project.name) as project_name,
  SUM(cost) as cost_before_credits,
  SUM(cost)+SUM(discounts)+SUM(promotions) as cost_after_credit,
  SUM(cost)+SUM(discounts) as cost_after_discount
FROM `ama.details_cost_credit`, 
UNNEST([DATE(DATETIME(usage_start_time, "America/Los_Angeles"))]) as usage_start_time
WHERE
  usage_start_time = DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
AND
  (
    project.name IS NULL
  OR
    project.name IN ("project1","project2","project3")
  )
GROUP BY usage_start_time, project.name

Upvotes: 1

Related Questions