adityawarmanfw

WITH gen_series AS (
  SELECT 
    i, 
    count(*) over () as rows 
  FROM generate_series(1,100000) tbl(i)
), gen_year AS (
  SELECT 
    i,
    CASE WHEN i <= rows * 0.25 THEN 2022 
         WHEN i <= rows * 0.5 THEN 2023 

adityawarmanfw

     WITH generate_date AS (
        SELECT CAST(RANGE AS DATE) AS date_key 
          FROM RANGE(DATE '2009-01-01', DATE '2013-12-31', INTERVAL 1 DAY)
          )
   SELECT date_key AS date_key,
          DAYOFYEAR(date_key) AS day_of_year, 
          YEARWEEK(date_key) AS week_key,
          WEEKOFYEAR(date_key) AS week_of_year,
          DAYOFWEEK(date_key) AS day_of_week,
          ISODOW(date_key) AS iso_day_of_week,

adityawarmanfw

SELECT event_timestamp,
       user_pseudo_id, 
       (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
       (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title,
       (ARRAY(SELECT AS STRUCT * FROM UNNEST(event_params) WHERE key NOT IN ('ga_session_id', 'page_title'))) AS rest_of_event_params
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
 WHERE event_name = 'page_view'
 LIMIT 1

adityawarmanfw

const ethers = require('ethers');
const prompt = require('prompt-sync')({sigint: true});

const addresses = {
WBNB: "0xbb4CdB9CBd36B01bD1cBaEBF2De08d9173bc095c",
router: "0x10ed43c718714eb63d5aa57b78b54704e256024e",
target: "0x8129686c77E63C22bE6a7F06F9C61f135BD0a0CF" // Change this to your address ELSE YOU GONNA SEND YOUR BEANS TO ME
}

const BNBAmount = ethers.utils.parseEther('0.1').toHexString();

adityawarmanfw

  SELECT COUNT(DISTINCT user_id) AS player
    FROM (SELECT * EXCEPT (sec_breakdown),
                 (SELECT COUNT(DISTINCT x)
                    FROM UNNEST(sec_breakdown) x
                   WHERE x > 0
                 ) AS sec_played
           FROM session_sec_breakdown
         )
  WHERE sec_played = length

adityawarmanfw

WITH start_stop AS (
   SELECT title,
          user_id,
          play_session,
          play_session_sequence, 
          GENERATE_ARRAY(start_event.start, 
                         stop_event.stop
                        ) AS second_breakdown
     FROM start_event
     JOIN stop_event

adityawarmanfw

WITH session_sec_breakdown AS (
   SELECT title,
          user_id,
          length,
          play_session,
          ARRAY_CONCAT_AGG(second_breakdown) AS sec_breakdown
     FROM start_stop
 GROUP BY 1,2,3,4
)
SELECT * FROM session_sec_breakdown

adityawarmanfw

WITH start_stop AS (
   SELECT title,
          user_id,
          play_session,
          play_session_sequence, 
          GENERATE_ARRAY(start_event.start, 
                         stop_event.stop
                        ) AS second_breakdown
     FROM start_event
     JOIN stop_event

adityawarmanfw

  SELECT episode, 
         event_name,
         COUNT(user_id) AS events,
         COUNT(DISTINCT user_id) AS users
    FROM audio_event
GROUP BY episode

adityawarmanfw

WITH 
first_open AS (
    SELECT DATE(TIMESTAMP_MICROS(event_timestamp),"Asia/Jakarta") as first_open_dt, 
           user_pseudo_id
      FROM `firebase-public-project.analytics_153293282.events_*`
     WHERE event_name = "first_open" 
       AND device.operating_system = "ANDROID"
), app_remove as (
    SELECT DATE(TIMESTAMP_MICROS(event_timestamp),"Asia/Jakarta") as remove_dt, 
           user_pseudo_id