with subquery_1 as(
       SELECT 
            SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(content_decode, ' ', ''), '"', ''), '\n', ''), '\r\n', ''), '\'', ''), 'blk:', -1), '}', 1) AS natNumber, 
            address, 
            number,
            block_time
       FROM 
            ordinals.inscriptions_mints
       WHERE 
            content_decode LIKE "%7709a1f343c4ef2c3a4f6fb19d1a9605e30b72bc3d3c8b771719c53480b376c7i0%" 
            AND json_to_map(content_decode) IS NOT NULL
)


,natMentedTable as (
        SELECT NATNumber, address, number, block_time, rn
    FROM (
        SELECT 
            CAST(natNumber AS INTEGER) AS NATNumber, 
            address, 
            number,
            block_time,
            ROW_NUMBER() OVER (PARTITION BY CAST(natNumber AS INTEGER) ORDER BY number ASC) AS rn
        FROM (
            SELECT 
                natNumber,
                address, 
                number,
                block_time
            FROM 
                subquery_1
            WHERE 
                natNumber REGEXP '^[0-9]+$'
                AND natNumber NOT REGEXP '^0[0-9]+$'
                AND TRIM(natNumber) <> ''
                AND CAST(natNumber AS INTEGER) <= (SELECT MAX(number) FROM bitcoin.blocks)
        ) AS subquery
    ) AS ranked
)


-- a.*, 
SELECT 
    CONCAT('{"p": "tap", "op": "dmt-mint", "dep": "7709a1f343c4ef2c3a4f6fb19d1a9605e30b72bc3d3c8b771719c53480b376c7i0", "tick": "omb", "blk": "', a.tick, '"}') AS code
    
FROM 
    ( 
        SELECT 
            number as tick
        FROM 
            bitcoin.blocks 
        WHERE bits like '%2b%'
        ORDER BY 
            number ASC 
    ) AS a 
    LEFT JOIN ( 
SELECT NATNumber as tick
FROM natMentedTable
WHERE rn = 1
ORDER BY natNumber ASC
    ) AS b ON a.tick = b.tick
WHERE b.tick IS NULL
ORDER BY a.tick ASC