Overview
========

Query
-----

```sql
WITH 
  table_1 AS (...),
  table_2 AS (...), -- Some Transformation on 1
  table_3 AS (...), -- Some Transformation on 2
  table_4 AS (...)  -- Some Transformation on 3 and a Join

  SELECT
    user_id,
    feature_names, 
    feature_values 
  FROM table_4
  WHERE uuid = "xxx"

```

Output
------

```
+---------+-----------------------------------------------+--------------------------------------------+
| user_id |                 feature_names                 |             feature_values                 |
+---------+-----------------------------------------------+--------------------------------------------+
|    1    | ["feature_a", "feature_b", ... , "feature_n"] | ["value_a1", "value_b1", ... , "value_n1"] |
|    2    | ["feature_a", "feature_b", ... , "feature_n"] | ["value_a2", "value_b2", ... , "value_n2"] |
|   ...   |                    ...                        |                   ...                      |
+---------+-----------------------------------------------+--------------------------------------------+

```

Desired Output
--------------

```
+---------+-----------+-----------+------+-----------+
| user_id | feature_a | feature_b |  ... | feature_n |
+---------+-----------+-----------+------+-----------+
|    1    | value_a1  | value_b1  |  ... | value_n1  |
|    2    | value_a2  | value_b2  |  ... | value_n2  |
|   ...   |    ...    |    ...    |  ... |    ...    |
+---------+-----------+-----------+------+-----------+

```

Constraints
-----------

1. I cannot do away with the CTEs as the transformations and joins are
too complex to be handled in a Single Query.
2. The array of `feature_names` can change based on the `uuid`. However, it will be constant for a given `uuid`. The following are the properties of the `feature_names`:

	* `feature_names` can be of varying lengths. i.e. One `uuid` can have 10 values in the array but another can have 5.
	* The values of `feature_names` are not fixed. i.e. Even if two `uuids` have the same number of features, it's not necessary that the names would be the same. One can have `[ "a", "b", "c" ]` and another can have `[ "x", "y", "z" ]`

Given the above constraints, it's clear that we really cannot "hardcode" the `feature_names` by doing something like:

```sql
WITH 
  table_1 AS (...),
  table_2 AS (...), -- Some Transformation on 1
  table_3 AS (...), -- Some Transformation on 2
  table_4 AS (...), -- Some Transformation on 3 and a Join

  table_5 AS (
  SELECT 
    user_id,
    JSON_VALUE_ARRAY(feature_values) as feature_values
  FROM table_4
  WHERE uuid = "xxx"
  )

  SELECT
    user_id
    feature_values[ORDINAL(1)] AS feature_a,
    feature_values[ORDINAL(2)] AS feature_b,
    ... - Extracting other Features by accessing array elements
    feature_values[ORDINAL(n)] AS feature_n,
  FROM table_5

```

Approaches Tried
================

Converting data to Long-Format and then to Wide-Format
------------------------------------------------------

### Long-Format Query

```sql
WITH 
  table_1 AS (...),
  table_2 AS (...), -- Some Transformation on 1
  table_3 AS (...), -- Some Transformation on 2
  table_4 AS (...), -- Some Transformation on 3 and a Join

  subset_table AS (
  SELECT 
    user_id,
    feature_names, 
    feature_values 
  FROM table_4
  WHERE uuid = "xxx"
  ),

  long_format AS (
  SELECT 
    *EXCEPT(feature_names, feature_values, pos1, pos2),
    feature_names,
    feature_values
  FROM final_data,
  UNNEST(JSON_VALUE_ARRAY(feature_names)) feature_names WITH OFFSET pos1,
  UNNEST(JSON_VALUE_ARRAY(feature_values)) feature_values WITH OFFSET pos2
  WHERE pos1 = pos2
  )

  SELECT * FROM long_format

```

### Long-Format Output

```
+---------+--------------+---------------+
| user_id | feature_name | feature_value |
+---------+--------------+---------------+
|     1   | feature_a    | value_a1      |
|     1   | feature_b    | value_b1      |
|     1   |    ...       |   ...         |
|     1   | feature_n    | value_n1      |
|     2   | feature_a    | value_a2      |
|     2   |    ...       |    ...        |
+---------+--------------+---------------+

```

### Issue when Converting Long to Wide Format

I tried declaring a constant and using `EXECUTE_IMMEDIATE` as illustrated in this [video](https://youtu.be/QWIGpplBUuQ), however, looks like if we are using CTEs, BigQuery only expects `SELECT` statements to be used. Therefore we cannot do:

```sql
DECLARE unique_features STRING;

... -- Code from the long-format query above

EXECUTE IMMEDIATE 
"""
SELECT 
    STRING_AGG(CONCAT("'",feature_name,"'")) 
FROM (SELECT DISTINCT feature_name FROM long_format)
""") INTO unique_features

EXECUTE IMMEDIATE format(
"""
SELECT 
  * 
FROM long_data
PIVOT(ANY_VALUE(feature_value) FOR feature_names IN(%s))
""", unique_features)

```

Any idea how can I achieve my desired outcome given the constraints?

**PS:** I will keep updating the **Approaches Tried** Section to keep you all posted.