CREATE TABLE temp_tables.tmpproviderpending(provider_id BIGINT, file_name VARCHAR(255) NOT NULL);
CREATE INDEX `IX_DBA` ON temp_tables.tmpproviderpending(provider_id,file_name);


INSERT INTO temp_tables.tmpproviderpending
SELECT provider_id,
       MAX(file_name) AS max_file_name
  FROM temp_tables.processor_sample
 WHERE rec_engine = 'PENDING'
 GROUP BY provider_id
 HAVING COUNT(*) > 1 AND COUNT(DISTINCT file_name) >1;
 
 DROP TABLE IF EXISTS temp_tables.tmpproviderpending;