-- This is the consumer side code for the basic consumer-run data analysis example.
-- To create and publish the clean room, run the provider code first, and then run the following code.
-- For context and more information, see docs.snowflake.com/user-guide/cleanrooms/demo-flows/basic-flow-data-analysis

USE WAREHOUSE app_wh;
USE ROLE samooha_app_role;

-- See only clean rooms that aren't installed.
-- NB: Doesn't work before installation
CALL samooha_by_snowflake_local_db.consumer.view_cleanrooms() ->>
  SELECT * FROM $1
    WHERE IS_ALREADY_INSTALLED = false;

SET cleanroom_name = 'Consumer analysis';

-- Install the clean room. This takes a few minutes.
-- Give your provider account locator where indicated.
CALL samooha_by_snowflake_local_db.consumer.install_cleanroom($cleanroom_name, '<PROVIDER_LOCATOR>');

-- Link your data into the clean room.
CALL samooha_by_snowflake_local_db.consumer.link_datasets($cleanroom_name, ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);

-- Set the join policy.
CALL samooha_by_snowflake_local_db.consumer.set_join_policy($cleanroom_name, ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:HASHED_EMAIL']);

-- Examine the templates.
CALL samooha_by_snowflake_local_db.consumer.view_added_templates($cleanroom_name);

SET template_name = 'prod_overlap_analysis';
-- See which variables you can pass in when you run this template.
CALL samooha_by_snowflake_local_db.consumer.get_arguments_from_template(
    $cleanroom_name,
    $template_name);

-- Set column policies.
CALL samooha_by_snowflake_local_db.consumer.set_column_policy($cleanroom_name,
  [
   $template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:STATUS',
   $template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:AGE_BAND',
   $template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:REGION_CODE',
   $template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:DAYS_ACTIVE',
   $template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:DEVICE_TYPE'
  ]);

-- See what data is available. --

-- See which datasets you have linked into the clean room.
CALL samooha_by_snowflake_local_db.consumer.view_consumer_datasets($cleanroom_name);

-- See which datasets the provider has linked into the clean room.
CALL samooha_by_snowflake_local_db.consumer.view_provider_datasets($cleanroom_name);

-- Run the analysis.
CALL samooha_by_snowflake_local_db.consumer.run_analysis(
  $cleanroom_name,
  'prod_overlap_analysis',
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'], -- Consumer tables.
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],  -- Provider tables.
  object_construct(                        -- Extra template-specific arguments here as name-value pairs, described previously.
    'dimensions', ['p.STATUS'],          -- Any GROUP BY columns to show in the results.
    'measure_column', ['p.DAYS_ACTIVE'], -- Any aggregation columns to show in the results.
    'measure_type', ['AVG'],             -- Aggregation functions to apply to measure_column columns.
    'where_clause', 'p.HASHED_EMAIL=c.HASHED_EMAIL'
  )
);

-- Clean up when done.
CALL samooha_by_snowflake_local_db.consumer.uninstall_cleanroom($cleanroom_name);
