Repository: unytics/bigfunctions (language: python)

Issue:
[new]: `udaf` compatibility - `weighted_average`(element, weight)
### Check the idea has not already been suggested

- [X] I could not find my idea in [existing issues](https://github.com/unytics/bigfunctions/issues?q=is%3Aissue+is%3Aopen+label%3Anew-bigfunction)

### Edit the title above with self-explanatory function name and argument names

- [X] The function name and the argument names I entered in the title above seems self explanatory to me.

### BigFunction Description as it would appear in the documentation

```sql
with
   _sample_data as (
      select 10 as grade, 1 as ponderation
      union all 
      select 13 as grade, 2 as ponderation
  )
select 
  `sandbox-athevenot`.`wrk_dataset`.weighted_average(grade, ponderation) as final_grade 
from `_sample_data`
```

### Examples of (arguments, expected output) as they would appear in the documentation

example above returns `12.0`


Discussion / hints:

Fix patch:
diff --git a/CONTRIBUTING.md b/CONTRIBUTING.md
index b8cde2a3..2fbfee60 100644
--- a/CONTRIBUTING.md
+++ b/CONTRIBUTING.md
@@ -24,7 +24,7 @@ To make a change to this repository:
 
 Before spending a lot of time to develop a new bigfunction, don't hesitate to reach out through [bigfunctions slack](https://join.slack.com/t/unytics/shared_invite/zt-1gbv491mu-cs03EJbQ1fsHdQMcFN7E1Q) (or by email --> paul.marcombes@unytics.io).
 
-To start, the best is to duplicate an existing yaml file in 'bigfunctions' folder. Make sure to duplicate a file with the same type (function_sql, function_js, function_py, procedure) as the one you target.
+To start, the best is to duplicate an existing yaml file in 'bigfunctions' folder. Make sure to duplicate a file with the same type (function_sql, aggregate_function_sql, function_js, function_py, procedure) as the one you target.
 
 Before submitting a Pull-Request, make sure:
 
@@ -45,4 +45,3 @@ You can contribute by selecting one of [these issues](https://github.com/unytics
 
 ## License
 By contributing to this repository, you agree that your contributions will be licensed under its MIT License.
-
diff --git a/bigfun/templates/aggregate_function_sql.sql b/bigfun/templates/aggregate_function_sql.sql
new file mode 100644
index 00000000..0c9cdc11
--- /dev/null
+++ b/bigfun/templates/aggregate_function_sql.sql
@@ -0,0 +1,8 @@
+create or replace aggregate function `{{ project }}`.`{{ dataset }}`.{{ name }}({% for argument in arguments %}`{{ argument.name }}` {{ argument.type}}{% if not loop.last %}, {% endif %}{% endfor %})
+{% if output.type != 'any type' %}returns {{ output.type }}{% endif %}
+as (
+{{ code | replace('{BIGFUNCTIONS_DATASET}',  '`' +  project + '`.`' + dataset + '`' ) }}
+)
+options(
+    description = '''{{ description }}'''
+)
diff --git a/bigfun/templates/bigfunction.md b/bigfun/templates/bigfunction.md
index f1ffaa27..029f57c4 100644
--- a/bigfun/templates/bigfunction.md
+++ b/bigfun/templates/bigfunction.md
@@ -71,7 +71,15 @@ description: "BigFunction {{ name }}: {{ description }}"
 === "{% if dataset|length <=2 %}{{ dataset | upper | replace('_', '-') }}{% else %}{{ dataset | replace('_', '-') }}{% endif %}"
 
     ```sql
+    {% if example.with_clause is defined %}
+    with sample_data as (
+      {{ example.with_clause | indent(2) }}
+    )
+    {% endif %}
     {% if type == 'procedure' %}call{% elif type == 'table_function' %}select * from{% else %}select{% endif %} {{ project }}.{{ dataset }}.{{ name }}({% for argument in example.arguments %}{{ argument | replace('{BIGFUNCTIONS_DATASET}', dataset) | replace('\n', '\n      ') }}{% if not loop.last %}, {% endif %}{% endfor %}){% if type == 'procedure' %};{% elif 'output' in bigfunction and type != 'table_function' %} as {{ output.name }}{% endif %}
+    {%- if example.with_clause is defined %}
+    from sample_data
+    {% endif %}
     {% if type == 'procedure' and template %}select html from bigfunction_result;{% endif %}
     {%- if type == 'procedure' and example.output %}select * from bigfunction_result;{% endif %}
     ```
@@ -84,9 +92,17 @@ description: "BigFunction {{ name }}: {{ description }}"
 {% for dataset in datasets %}
 
 ```sql
-{% if type == 'procedure' %}call{% elif type == 'table_function' %}select * from{% else %}select{% endif %} {{ project }}.{{ dataset }}.{{ name }}({% for argument in example.arguments %}{{ argument | replace('{BIGFUNCTIONS_DATASET}', dataset) | replace('\n', '\n  ') }}{% if not loop.last %}, {% endif %}{% endfor %}){% if type == 'procedure' %};{% elif 'output' in bigfunction and type != 'table_function' %} as {{ output.name }}{% endif %}
+{% if example.with_clause is defined %}
+with sample_data as (
+  {{ example.with_clause | indent(2) }}
+)
+{% endif %}{% if type == 'procedure' %}call{% elif type == 'table_function' %}select * from{% else %}select{% endif %} {{ project }}.{{ dataset }}.{{ name }}({% for argument in example.arguments %}{{ argument | replace('{BIGFUNCTIONS_DATASET}', dataset) | replace('\n', '\n  ') }}{% if not loop.last %}, {% endif %}{% endfor %}){% if type == 'procedure' %};{% elif 'output' in bigfunction and type != 'table_function' %} as {{ output.name }}{% endif %}
+{%- if example.with_clause is defined %}
+from sample_data
+{% endif %}
 {% if type == 'procedure' and template %}select html from bigfunction_result;{% endif %}
 {%- if type == 'procedure' and example.output %}select * from bigfunction_result;{% endif %}
+
 ```
 
 {% endfor %}
@@ -129,5 +145,3 @@ description: "BigFunction {{ name }}: {{ description }}"
 
 {% if example.screenshot %}<a href="{{ example.screenshot }}"><img alt="screenshot" src="{{ example.screenshot }}" style="border: var(--md-code-bg-color) solid 1rem; margin-top: -1rem; width: 100%"></a>{% endif %}
 {% endfor %}
-
-
diff --git a/bigfunctions/weighted_average.yaml b/bigfunctions/weighted_average.yaml
new file mode 100644
index 00000000..6e16071d
--- /dev/null
+++ b/bigfunctions/weighted_average.yaml
@@ -0,0 +1,31 @@
+type: aggregate_function_sql
+category: transform_numeric
+author:
+  name: Axel Thevenot
+  url: https://www.linkedin.com/in/axel-thevenot/
+  avatar_url: "https://avatars.githubusercontent.com/u/39374103?v=4"
+description: |
+  Returns the weigthed average elements.
+arguments:
+  - name: element
+    type: float64
+  - name: weight
+    type: float64
+output:
+  name: weighted_average
+  type: float64
+examples:
+  - description: "Calculate average unit price"
+    with_clause: |
+        select 10 as grade, 1 as ponderation
+        union all
+        select 13 as grade, 2 as ponderation
+    arguments:
+      - grade
+      - ponderation
+    output: "12"
+    region: ALL
+code: |
+  (
+    sum(element * weight) / sum(weight)
+  )


Test patch:
diff --git a/bigfun/templates/aggregate_function_sql_test.sql b/bigfun/templates/aggregate_function_sql_test.sql
new file mode 100644
index 00000000..ff5f120c
--- /dev/null
+++ b/bigfun/templates/aggregate_function_sql_test.sql
@@ -0,0 +1,1 @@
+select `{{ project }}`.`{{ dataset }}`.{{ name }}({% for argument in example.arguments %}{{ argument | replace('{BIGFUNCTIONS_DATASET}',  '`' +  project + '`.`' + dataset + '`' ) }}{% if not loop.last %}, {% endif %}{% endfor %}) as {{ output.name }}