FREE AI-Enhanced Online SQLite Compiler

Untitled*

You must execute the code again to save those values in the database.

WITH
elements
    (grp,value) AS ( VALUES
    ('a',1    ),
    ('a',2    ),
    ('b',9    ),
    ('b',3    ),
    ('b',2    )
),
base AS
(
    SELECT
        grp  ,
        value,
        COUNT(*) OVER (
            PARTITION BY grp) AS grp_count
    FROM
        elements
),
combinations AS
(
    SELECT
                       grp            ,
                       value          ,
        grp_count   AS partition_index,
        value || '' AS path
    FROM
        base

UNION

SELECT
                          prior.grp            ,
                        current.value          ,
        partition_index - 1  AS partition_index,
        path          ||
        '.'           ||
        current.value        AS path
    FROM
        combinations AS prior
    JOIN
        base AS current
        ON
        prior.grp IS current.grp
    WHERE
        partition_index >  1             AND
        prior.value     >= current.value
),
bounded AS
(
    SELECT
        grp            ,
        value          ,
        path           ,
        partition_index,

REPLACE(path,'.',':') || ':' AS upper_bound ,
        path || ':'                  AS compare_path,
        path || '.'                  AS lower_bound
    FROM
        combinations
),
combination_sets AS
(
    SELECT
        grp,
        path,
        upper_bound,
        lower_bound
    FROM
        bounded
    WHERE
        partition_index IS 1
),
comparisons1 AS
(
    SELECT
        sets.grp ,
        sets.path,
        sets.lower_bound,
             compare_path,
        sets.upper_bound,
        sets.path LIKE (elements.path || '%')     AS like_match,
        sets.lower_bound <= elements.compare_path AS lower_bound_match,
        elements.compare_path <= sets.upper_bound AS upper_bound_match
    FROM
        combination_sets AS sets
    JOIN
        bounded AS elements
        ON
        sets.grp IS elements.grp
),
comparisons2 AS
(
    SELECT
        grp ,
        path,
        lower_bound,
        compare_path,
        upper_bound,
        like_match,
        lower_bound_match,
        upper_bound_match,
        lower_bound_match AND upper_bound_match AS bounds_match
    FROM
        comparisons1
)
SELECT
    'like_match IS NOT bounds_match' AS Error,
    *
FROM
    comparisons2
WHERE
    like_match IS NOT bounds_match

Error	grp	path	lower_bound	compare_path	upper_bound	lower_bound_match	upper_bound_match	bounds_match
like_match IS NOT bounds_match	a	2.1	2.1.	2.2:	2:1:	1	1	1
like_match IS NOT bounds_match	b	9.9.3	9.9.3.	9.9.9:	9:9:3:	1	1	1
like_match IS NOT bounds_match	b	9.9.2	9.9.2.	9.9.3:	9:9:2:	1	1	1
like_match IS NOT bounds_match	b	9.9.2	9.9.2.	9.9.9:	9:9:2:	1	1	1
like_match IS NOT bounds_match	b	9.3.3	9.3.3.	9.9.2:	9:3:3:	1	1	1
like_match IS NOT bounds_match	b	9.3.3	9.3.3.	9.9.3:	9:3:3:	1	1	1
like_match IS NOT bounds_match	b	9.3.3	9.3.3.	9.9.9:	9:3:3:	1	1	1
like_match IS NOT bounds_match	b	9.3.3	9.3.3.	9.9:	9:3:3:	1	1	1
like_match IS NOT bounds_match	b	9.3.2	9.3.2.	9.9.2:	9:3:2:	1	1	1
like_match IS NOT bounds_match	b	9.3.2	9.3.2.	9.3.3:	9:3:2:	1	1	1
like_match IS NOT bounds_match	b	9.3.2	9.3.2.	9.9.3:	9:3:2:	1	1	1
like_match IS NOT bounds_match	b	9.3.2	9.3.2.	9.9.9:	9:3:2:	1	1	1
like_match IS NOT bounds_match	b	9.3.2	9.3.2.	9.9:	9:3:2:	1	1	1
like_match IS NOT bounds_match	b	9.2.2	9.2.2.	9.3.2:	9:2:2:	1	1	1
like_match IS NOT bounds_match	b	9.2.2	9.2.2.	9.9.2:	9:2:2:	1	1	1
like_match IS NOT bounds_match	b	9.2.2	9.2.2.	9.3.3:	9:2:2:	1	1	1
like_match IS NOT bounds_match	b	9.2.2	9.2.2.	9.9.3:	9:2:2:	1	1	1
like_match IS NOT bounds_match	b	9.2.2	9.2.2.	9.3:	9:2:2:	1	1	1
like_match IS NOT bounds_match	b	9.2.2	9.2.2.	9.9.9:	9:2:2:	1	1	1
like_match IS NOT bounds_match	b	9.2.2	9.2.2.	9.9:	9:2:2:	1	1	1
like_match IS NOT bounds_match	b	3.3.2	3.3.2.	3.3.3:	3:3:2:	1	1	1
like_match IS NOT bounds_match	b	3.2.2	3.2.2.	3.3.2:	3:2:2:	1	1	1
like_match IS NOT bounds_match	b	3.2.2	3.2.2.	3.3.3:	3:2:2:	1	1	1
like_match IS NOT bounds_match	b	3.2.2	3.2.2.	3.3:	3:2:2:	1	1	1