phbrophy

BEGIN

# Dummy data, we'll replace this with the actual population of Flipped Accounts when we have the starting point.
CREATE OR REPLACE TEMP TABLE flipped_accounts AS (
  SELECT shop_id
    ,user_id
    ,DATE(TIMESTAMP_SECONDS(open_date)) AS open_date
  FROM `etsy-data-warehouse-prod.etsy_shard.shop_data`
  WHERE DATE(TIMESTAMP_SECONDS(open_date)) >= "2025-09-01"
);

phbrophy

BEGIN

CREATE OR REPLACE TEMPORARY TABLE spa_reserve AS (
    SELECT
        a.user_id
        ,a.ledger_balance as ledger_balance_ledger_currency
        ,a.currency as ledger_currency
        ,a.reserve_minimum as reserve_minimum_ledger_currency
        ,round(a.reserve_minimum * (coalesce(usd_fx.market_rate/1e7,1)),0) as reserve_minimum_usd
        ,round(a.reserve_minimum * (coalesce(eur_fx.market_rate/1e7,1)),0) as reserve_minimum_eur

phbrophy

BEGIN

CREATE OR REPLACE TEMPORARY TABLE spa_reserve_backfill AS (
    SELECT
        a.user_id
        ,a.ledger_balance as ledger_balance_ledger_currency
        ,a.currency as ledger_currency
        ,a.reserve_minimum as reserve_minimum_ledger_currency
        ,round(a.reserve_minimum * (coalesce(usd_fx.market_rate/1e7,1)),0) as reserve_minimum_usd
        ,round(a.reserve_minimum * (coalesce(eur_fx.market_rate/1e7,1)),0) as reserve_minimum_eur

phbrophy

BEGIN

CREATE TABLE IF NOT EXISTS `etsy-data-warehouse-dev.pbrophy.collusion_fraud_pairs` (
  buyer_user_id INT64
  ,seller_user_id INT64
  ,receipt_id INT64
  ,order_tsz TIMESTAMP
  ,order_date INT64
  ,receipt_usd_total_price NUMERIC
  ,receipt_usd_shipping_price NUMERIC

phbrophy

BEGIN 

CREATE TEMP TABLE all_signatures_wide AS(
  SELECT a.user_id
    ,a.country_address
    ,a.country_bank_account
    ,a.country_credit_card
    ,a.country_shop_setting
    ,a.country_login
    ,a.primary_language

phbrophy

-- owner: pbrophy@etsy.com
-- owner_team: ts-analytics@etsy.com
-- description: This rollup focuses on Seller Risk actions by Admin, manual controls, and automated controls.
-- access: etsy-data-warehouse-prod.rollups.seller_risk_reviews_and_actions=group:gcp-etsy-employees@etsy.com,group:bigquery-public-role@etsy.com
-- This table brings together all actions and reviews within Seller Risk's purview
-- This includes all actions taken automatically or manually
-- A review is an account review in atlas. This is either a structured review (has a Superbit query linked) or unstructured (does not have a Superbit query linked, and was surfaced in Witness some other way.) 

BEGIN

phbrophy

SELECT DATE(TIMESTAMP_SECONDS(create_date)) AS create_date
  ,user_id
  ,target_id AS shop_id
  ,REGEXP_EXTRACT(reason, r'"weighted_score":"([0-9.]+)"') AS weighted_score
  ,REGEXP_EXTRACT(reason, r'"spa_balance_usd":"([0-9.]+)"') AS spa_balance_usd
  ,REGEXP_EXTRACT(reason, r'"sbrm_score":"([0-9.]+)"') AS sbrm_score
  ,REGEXP_EXTRACT(reason, r'"precision":"([0-9.]+)"') AS precision
FROM `etsy-data-warehouse-prod.etsy_aux.flag`
WHERE flag_type_id = 1205790907763 -- subject = 'Risky Payout SBRM'
  AND target_type = 'shop'

phbrophy

BEGIN

CREATE TEMP TABLE cohort AS (
  SELECT user_id
    ,shop_id
    ,open_date
    ,DATE_DIFF(CURRENT_DATE, open_date, DAY) AS shop_age
    ,first_sale_date
    ,total_gms
  FROM `etsy-data-warehouse-prod.rollups.seller_basics`

phbrophy

# Determines BR loss, BR / TPV, case rate, and rolling case rate within 3 / 7 / 14 / 30 days for orders placed between 7/1/22 and 7/1/23.

BEGIN

DECLARE begin_date DATE DEFAULT "2022-07-01";
DECLARE end_date DATE DEFAULT "2023-07-01";

CREATE TEMP TABLE orders AS (
  SELECT receipt_id
    ,receipt_usd_total_price

phbrophy

# First Listing Bug exclusion.
BEGIN 

CREATE TEMP TABLE first_listing AS (
  WITH first_listing_dates AS (
    SELECT a.user_id
      ,DATE(TIMESTAMP_SECONDS(a.original_create_date)) AS listing_create_date
    FROM `etsy-data-warehouse-prod.etsy_shard.listings` a 
    QUALIFY ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY listing_create_date ASC)=1 
  )