copy-data.sql

insert into "371fc126-e213-4191-a7d0-6ab73517e001"."kpi_league_round_data" 
  select 
    '371fc126-e213-4191-a7d0-6ab73517e001' as org_id, 
    '638a2ee0-891d-11ec-b5f9-ab4b3e83052d' as league_id, 
    '638a2ee1-891d-11ec-b5f9-ab4b3e83052d' as season_id, 
    '638a7d01-891d-11ec-b5f9-ab4b3e83052d' as round_id, 
    ptcpts.division_id as division_id, 
    ptcpts.fixture_id as fixture_id, 
    s.source_key as source_key, 
    now() as copied_from_datasource_at, 
    ptcpts.participant_type as participant_type, 
    ptcpts.participant_id as participant_id, 
    ptcpts.participant_external_id as participant_external_id, 
    s.participant_data_source_id as participant_data_source_id, 
    cast(s.timestamp as timestamp without time zone) 
      as timestamp, 
    cast(s.kpi_measure as double precision) 
      as kpi_measure, 
    cast(s.scaling_factor_measure as double precision) 
      as scaling_factor_measure 
  from 
    (select 
       yapster_file || ':' || yapster_row::text  
         as source_key, 
        "store_id"  
          as participant_data_source_id, 
       cast(  to_timestamp( "timestamp" , 'YYYY-MM-DDTHH:MI:SS')  as timestamp without time zone) 
         as timestamp, 
        "desserts"  as kpi_measure, 
        "covers"  as scaling_factor_measure 
     from "371fc126-e213-4191-a7d0-6ab73517e001"."stray" 
     where 
       cast(  to_timestamp( "timestamp" , 'YYYY-MM-DDTHH:MI:SS')  as timestamp without time zone  ) 
         >= '2021-12-03T00:00:00.000Z' 
       and cast(  to_timestamp( "timestamp" , 'YYYY-MM-DDTHH:MI:SS')  as timestamp without time zone ) 
         < '2021-12-04T00:00:00.000Z' 
    ) as s 
  inner join 
    (select * from 
       (VALUES ('371fc126-e213-4191-a7d0-6ab73517e001','638a2ee0-891d-11ec-b5f9-ab4b3e83052d','user','marsh','101'),
('371fc126-e213-4191-a7d0-6ab73517e001','638a2ee0-891d-11ec-b5f9-ab4b3e83052d','user','micro','998'),
('371fc126-e213-4191-a7d0-6ab73517e001','638a2ee0-891d-11ec-b5f9-ab4b3e83052d','user','also','100'),
('371fc126-e213-4191-a7d0-6ab73517e001','638a2ee0-891d-11ec-b5f9-ab4b3e83052d','user','great','999'))
 as id_mapping
 ("org_id","league_id","participant_type","participant_external_id","participant_data_source_id") 
     where 
       org_id='371fc126-e213-4191-a7d0-6ab73517e001' 
       and league_id='638a2ee0-891d-11ec-b5f9-ab4b3e83052d') as league_id_mapping 
    on league_id_mapping.participant_data_source_id 
         =s.participant_data_source_id 
  inner join 
    (VALUES ('371fc126-e213-4191-a7d0-6ab73517e001','638a2ee0-891d-11ec-b5f9-ab4b3e83052d','638a2ee2-891d-11ec-b5f9-ab4b3e83052d',NULL,'user','638aa411-891d-11ec-b5f9-ab4b3e83052d','marsh'),
('371fc126-e213-4191-a7d0-6ab73517e001','638a2ee0-891d-11ec-b5f9-ab4b3e83052d','638a55f1-891d-11ec-b5f9-ab4b3e83052d',NULL,'user','638aa413-891d-11ec-b5f9-ab4b3e83052d','micro'),
('371fc126-e213-4191-a7d0-6ab73517e001','638a2ee0-891d-11ec-b5f9-ab4b3e83052d','638a2ee2-891d-11ec-b5f9-ab4b3e83052d','638a7d02-891d-11ec-b5f9-ab4b3e83052d','user','638a55f0-891d-11ec-b5f9-ab4b3e83052d','also'),
('371fc126-e213-4191-a7d0-6ab73517e001','638a2ee0-891d-11ec-b5f9-ab4b3e83052d','638a55f1-891d-11ec-b5f9-ab4b3e83052d','638aa410-891d-11ec-b5f9-ab4b3e83052d','user','638a55f2-891d-11ec-b5f9-ab4b3e83052d','great'))
 as ptcpts
 ("org_id","league_id","division_id","fixture_id","participant_type","participant_id","participant_external_id") 
    on ptcpts.participant_type=league_id_mapping.participant_type 
    and ptcpts.participant_external_id 
        =league_id_mapping.participant_external_id