wizkids121 wizkids121 - 5 months ago 11
SQL Question

BigQuery using MAX() function

So I have a query in BQ that looks as such:

SELECT
SubscriptionId,
start_time,
STRFTIME_UTC_USEC((UTC_USEC_TO_MONTH(TIMESTAMP_TO_USEC(TIMESTAMP(start_time)))),'%B %Y') AS cohort_month,
UTC_USEC_TO_MONTH(start_time) AS usec_month,
STRFTIME_UTC_USEC((UTC_USEC_TO_WEEK(TIMESTAMP_TO_USEC(TIMESTAMP(start_time)), 0)),'%Y-%m-%d') AS cohort_week,
WEEK(start_time) AS usec_week,
DATE(start_time) AS cohort_day,
UTC_USEC_TO_DAY(start_time) AS usec_day,
amount,
current_period_start,
current_period_end,
cancel_date,
end_date,
cancel_at_period_end,
salesRepEmail,
CASE WHEN (salesRepEmail IS NOT NULL) THEN 'Telesales' ELSE 'Online' END AS sales_channel,
status,
type_id,
CASE WHEN (type_id IN ('150032',
'150033',
'150023')) THEN 'Annual' ELSE 'Monthly' END AS duration,
refunded
FROM
[data_snapshots_daily.subs_charges_refunds_]
WHERE
start_time >= '2016-04-01 00:00:00'
AND refunded = FALSE


What I'm looking to do though, is add on to the query so that it returns all the relevant data from the most recent month, week, and day.

So I imagine it involves something to do with MAX(usec_month) but I can't figure it out. Remember, I only want it to return relevant data when it's included in the most recent month (June)

Answer

i think of something like below

for current month

WHERE YEAR(CURRENT_DATE()) = YEAR(start_time)
AND MONTH(CURRENT_DATE()) = MONTH(start_time)

for current week

WHERE YEAR(CURRENT_DATE()) = YEAR(start_time)
AND WEEK(CURRENT_DATE()) = WEEK(start_time)

for current day

WHERE CURRENT_DATE() = DATE(start_time)

quick add

for last two weeks play with something like below (should be improved to handle first week of the year)

WHERE (YEAR(CURRENT_DATE()) = YEAR(start_time) AND WEEK(CURRENT_DATE()) = WEEK(start_time))
OR CASE WHEN WEEK(CURRENT_DATE()) = 1 
      THEN (YEAR(CURRENT_DATE()) - 1 = YEAR(start_time) AND 53 = WEEK(start_time))
      ELSE (YEAR(CURRENT_DATE()) = YEAR(start_time) AND WEEK(CURRENT_DATE()) - 1 = WEEK(start_time))
    END
Comments