thiscodeWorks - Organizing the best of code online

List view

select           cast(campaign_date as date) as spikedate
                ,case when cast(campaign_date as date) = date'2021-11-11' then '1111'
                      when is_big_campaign =  '1' and cast(campaign_date as date) <> date'2021-11-11' then 'Spike'
                      when is_big_campaign =  '0'  then 'Mini Spike' end as SpikeFlag
from            vnbi_bd.clusterbi_spike_days
where           ingestion_timestamp = (select max(ingestion_timestamp) m from shopee_vn.shopee_vn_bi_team__fmcgbi_campaign_day)
                and cast(campaign_date as date) >= date'2020-10-10'

#syntax #extract #cast

Access Table List

vnbi_fpna.shopee_vn_bi_team__monthly_voucher_deal_airpay
vnbi_fpna.shopee_vn_bi_team__airpay_collection_sponsor_data

#syntax #extract #cast

cast value

try_cast(o.grass_date as date) grass_date,
try_cast(itemid as bigint) item_id,

#syntax #extract

extract keywords on a list

array_sort(
  array_distinct(
    regexp_extract_all(
      lower(it.name)
      ,'\bkhô gà\b|\bhạt điều rang muối\b|\bda cá\b'
    )))

#syntax #datetimetodate

datetime to date

 date(split(o.create_datetime, ' ')[1])

#syntax #lastnday

last n day

date_add('day',-1,current_date)

spike day

Access Table List

cast value

extract keywords on a list

datetime to date

last n day

Save snippets that work with our extensions