TO aggregate action strings IN Presto SQL WHILE prioritizing occurrences (1) over non-occurrences (0) FOR each DAY, you can follow these steps:

Determine the Maximum LENGTH: Find the maximum LENGTH OF the action strings within each GROUP (e.g., BY GROUP_ID) TO align the strings properly.

Left-Pad Strings: Pad each action string ON the left WITH zeros ('0') so that ALL strings have the same LENGTH. This ensures the rightmost entries (most recent days) are aligned.

CONVERT Strings TO Arrays: Split each padded string INTO an ARRAY OF integers (0 OR 1) representing each DAY's action.

Aggregate Arrays: Use the reduce function along with zip_with to perform an element-wise logical OR operation across all arrays within each group.

Convert Back to String: Join the resulting aggregated array back into a string to represent the combined action string for the group.
________________________________________________________

Here's the complete SQL query implementing the above steps:

WITH max_lengths AS (
  SELECT GROUP_ID, MAX(LENGTH(action_string)) AS max_length
  FROM your_table
  GROUP BY GROUP_ID
),
padded_strings AS (
  SELECT
    t.GROUP_ID,
    LPAD(t.action_string, l.max_length, '0') AS padded_action_string,
    l.max_length
  FROM your_table t
  JOIN max_lengths l ON t.GROUP_ID = l.GROUP_ID
),
arrays AS (
  SELECT
    GROUP_ID,
    TRANSFORM(
      REGEXP_EXTRACT_ALL(padded_action_string, '.'),
      x -> CAST(x AS INTEGER)
    ) AS bits_array,
    max_length
  FROM padded_strings
)
SELECT
  GROUP_ID,
  ARRAY_JOIN(
    REDUCE(
      ARRAY_AGG(bits_array),
      ARRAY_REPEAT(0, MAX(max_length)),
      (s, x) -> ZIP_WITH(s, x, (a, b) -> IF(a = 1 OR b = 1, 1, 0)),
      s -> s
    ),
    ''
  ) AS aggregated_action_string
FROM arrays
GROUP BY GROUP_ID;
________________________________________________________
Explanation:

max_lengths CTE: Calculates the maximum LENGTH OF action strings FOR each GROUP_ID.

padded_strings CTE: Pads each action string ON the left WITH zeros TO match the maximum LENGTH.

arrays CTE: Converts each padded action string INTO an ARRAY OF integers.

Main Query:

ARRAY_AGG(bits_array): Collects ALL bits arrays FOR each GROUP_ID.
REDUCE FUNCTION: Performs an element-wise logical OR across ALL arrays using ZIP_WITH.
Initial VALUE: An ARRAY OF zeros WITH LENGTH max_length.
Lambda FUNCTION: Combines arrays BY checking IF either bit IS 1.
ARRAY_JOIN: Converts the aggregated bits ARRAY back INTO a string.
Note: REPLACE your_table WITH the actual name OF your TABLE.

This query ensures that FOR each DAY (represented BY the position IN the string), the aggregated string will have a '1' IF ANY OF the input strings had a '1' AT that position, effectively prioritizing actions happening over NOT happening.