mshakhomirov

config {
  type: "operations",
  hasOutput: true,
  schema: "production",
  name: "test_user_reputation",
  disabled: false,
  dependencies: [ "reputation_data", "reputation_data_v", "user_reputation" ],
  tags: ["unit_tests"],
  description: "Advanced unit test to test complex query operations.",
}

mshakhomirov

config {
  type: "operations",
  hasOutput: true,
  schema: "production",
  disabled: false,
  name: "user_reputation",
  dependencies: ["reputation_data", "reputation_data_v"],
  tags: ["user_reputation"],
  description: "user_reputation based on reputation_data from firehose"
}

mshakhomirov

config {
  type: "test",
  dataset: "reputation_data_v"
}
-- use input to mock some test data for production.reputation_data table used by the view:
input "reputation_data" {
    select
  '{"user_id":1,"reputation_level":3.5,"updated_at":"1999-01-01 00:00:01 UTC"}' as src
}
-- run the view and we expect to get:

mshakhomirov

config { type: "test", dataset: "reputation_data_v" } -- use input to mock some test data for production.reputation_data table used by the view: input "reputation_data" { select '{"user_id":1,"reputation_level":3.5,"updated_at":"1999-01-01 00:00:01 UTC"}' as src } -- run the view and we expect to get:

mshakhomirov

config {
  type: "view",
  name: "reputation_data_v",
  schema: "production",
  dependencies: "reputation_data",
  tags: ["reputation_data"]
}

select 
     cast(json_extract(src,'$.user_id') as int64)                as user_id          

mshakhomirov

  config {
  type: "operations",
  hasOutput: true,
  schema: "production",
  disabled: false,
  name: "reputation_data",
  tags: ["reputation_data"],
  description: "reputation_data pipeline real-time updates from events."

}

mshakhomirov

with events as (
  select  'open_chat' as event_name, '10.1.0' as app_display_version union all
  select  'open_chat' as event_name, '10.1.9' as app_display_version union all
  select  'open_chat' as event_name, '9.1.4' as app_display_version union all
  select  'open_chat' as event_name, '9.0.0' as app_display_version
)
select
     app_display_version
    ,REGEXP_EXTRACT(app_display_version, '^[^.^]*') main_version
    ,safe_cast(REGEXP_EXTRACT(app_display_version, '[0-9]+.[0-9]+') as float64) release_version

mshakhomirov

-- One or more digits (\d+), optional period (\.?), zero or more digits (\d*).
with object as
(select  '{"aed":3.6732,"afn":78.45934,"all":110.586428}' as rates)

, data as (
select "usd" as base_currency,
  regexp_extract_all(rates, r'"[^"]+":\d+\.?\d*') as pair
from object
)
, splits as (

mshakhomirov

with d as (
select * from unnest([
  struct('0003f' as user_pseudo_id, 12322175 as user_id, timestamp '2020-10-10 16:46:59.878 UTC' as event_timestamp, 'join_group' as event_name),
  ('0003',12,timestamp '2022-10-10 16:50:03.394 UTC','set_avatar'),
  ('0003',12,timestamp '2022-10-10 17:02:38.632 UTC','set_avatar'),
  ('0003',12,timestamp '2022-10-10 17:09:38.645 UTC','set_avatar'),
  ('0003',12,timestamp '2022-10-10 17:10:38.645 UTC','join_group'),
  ('0003',12,timestamp '2022-10-10 17:15:38.645 UTC','create_group'),
  ('0003',12,timestamp '2022-10-10 17:17:38.645 UTC','create_group'),
  ('0003',12,timestamp '2022-10-10 17:18:38.645 UTC','in_app_purchase'),

mshakhomirov

select * from unnest([
        struct
        (     
            1                                 as user_id
        ,   111                               as reputation
        ,   timestamp('2021-12-16 13:00:01')  as update_time
        
        ),

        (