FQ5 Query

fq5-query.sql

select 
	id, 
	case 
		when ((dt_bit & 31) = 31) then 5
		when ((dt_bit & 15) = 15) then 4
		when ((dt_bit &  7) =  7) then 3
		when ((dt_bit &  3) =  3) then 2
		when ((dt_bit &  1) =  1) then 1
		else 0
	end as fq
from(
	select 
		id,
		sum(dt_bit) as dt_bit
	from(
		select 
			id, 
			case
				when dt = STRFTIME_UTC_USEC(DATE_ADD(CURRENT_TIMESTAMP(),  0, 'DAY'), '%Y%m%d') then 1
				when dt = STRFTIME_UTC_USEC(DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), '%Y%m%d') then 2
				when dt = STRFTIME_UTC_USEC(DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), '%Y%m%d') then 4
				when dt = STRFTIME_UTC_USEC(DATE_ADD(CURRENT_TIMESTAMP(), -3, 'DAY'), '%Y%m%d') then 8
				when dt = STRFTIME_UTC_USEC(DATE_ADD(CURRENT_TIMESTAMP(), -4, 'DAY'), '%Y%m%d') then 16
				else 0
			end as dt_bit
		from 
			// 指定日を含む５日分のテーブルを集計対象とする
			TABLE_DATE_RANGE(dau_, 
					DATE_ADD(CURRENT_TIMESTAMP(), -4, 'DAY'),
					CURRENT_TIMESTAMP()
			) dau
	)
	group by id
)