I would try to run a few parts separately, seeing if they fail, or looking for min/max values.  The overflow sounds like it would be happening on a conversion, aggregation, or insert process.

For example, run just this part:

select cast(to_char(day,'YYYYMMDD') as int) as date_id

from stage_click

and see if that is what is giving you issues.  If it does not fail, get the min/max values from date_id that is generated and see if they are beyond the definition of an int.

Additionally, double check the definition of your target table compared to your source data.  Is ID really an int? or a bigint in the source data?

We had a case of multi-billion-row sifting for bogus dates in files - namelly that they had illegal/garbage characters, some of which did not display

a quick way to scan your data for bogus values is to load the data into a varchar, and then follow up with the function as so:

when (translate(trim(column_name), '-:/0123456789','')='') then date(colum_name) else null end column_name

the above checks for valid chars in our date-type values, so just give it a character list that makes send for your data type - if all the characters reviewed end up forming an empty string, then the entire value "passes" and it's a keeper - otherwise do something else with it (as in the above, we made it null) but I am not a big fan of nulls in the final target - our downstream processing scrubbed nulls before the repository received them

In addition, when converting from the varchar, capture any values with a string length of > 8 - this will help you drive out the culprits and discover any common patterns in the bad data.