Adding percentile_cont and percentile_disc macros #1053
Description
Describe the feature
Introduce percentile_cont and percentile_disc macros to enable users to calculate percentile values in a cross-database compatible way. These macros would simplify percentile calculations across different SQL dialects.
Describe alternatives you've considered
Currently, percentile calculations are database-specific. For example, Redshift uses WITHIN GROUP (https://docs.aws.amazon.com/redshift/latest/dg/r_PERCENTILE_CONT.html), while BigQuery uses a different syntax (https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions#percentile_cont). Implementing macros would unify these operations across databases.
Additional context
The main challenge is the database-specific nature of percentile calculations.
Who will this benefit?
This feature would benefit anyone migrating to BigQuery from Postgres, Redshift or Snowflake, or from BigQuery to any of these databases, and who relies on percentile_cont or percentile_disc in their transformations.
Are you interested in contributing this feature?
Yes – I already have a working example and am happy to contribute.