WITH T1 AS (

SELECT

CASE

WHEN

INPUTS[0]['type'] = 'private' OR INPUTS[1]['type'] = 'private' OR INPUTS[2]['type'] = 'private' OR INPUTS[3]['type'] = 'private' THEN 'private' ELSE 'public'END AS Typee ,

CASE

WHEN INPUTS[0]['value'] like '%aleo%' THEN INPUTS[0]['value']

WHEN INPUTS[1]['value'] like '%aleo%' THEN INPUTS[1]['value']

WHEN INPUTS[2]['value'] like '%aleo%' THEN INPUTS[2]['value']

WHEN INPUTS[3]['value'] like '%aleo%' THEN INPUTS[3]['value']

WHEN INPUTS[4]['value'] like '%aleo%' THEN INPUTS[4]['value']

WHEN INPUTS[5]['value'] like '%aleo%' THEN INPUTS[5]['value'] END AS User ,

FROM

aleo.core.fact_transitions

WHERE

SUCCEEDED = 'TRUE'

and

Typee = 'public'

),

users AS

(SELECT

min(BLOCK_TIMESTAMP) AS Datee,

USER

FROM

T1

GROUP BY 2)

SELECT

Datee::DATE AS Date ,

COUNT(*) AS "New users",

SUM("New users") OVER (ORDER BY DATE) AS "Total users"

FROM