I am working on a project where I need to use the “WITH” clause to create CTE’s in my queries. This works well in MSSQL, but I found out that GridDB does not support the “WITH” clause. I tried using below mentioned way:

def top_customers(gridstore):
    query = """
    WITH HighValueCustomers AS (
        SELECT 
            CustomerID
        FROM 
            Orders
        GROUP BY 
            CustomerID
        HAVING 
            COUNT(*) > 15
    ),
    AverageOrderAmount AS (
        SELECT 
            CustomerID,
            AVG(OrderAmount) AS AvgOrderAmount
        FROM 
            Orders
        GROUP BY 
            CustomerID
    ),
    MostValuableCustomers AS (
        SELECT 
            hao.CustomerID
        FROM 
            AverageOrderAmount hao
        JOIN 
            HighValueCustomers hv ON hao.CustomerID = hv.CustomerID
        WHERE 
            hao.AvgOrderAmount > 100
    ),
    TopCustomers AS (
        SELECT 
            pc.CustomerID,
            COUNT(*) AS TotalOrders
        FROM 
            Orders o
        JOIN 
            MostValuableCustomers pc ON o.CustomerID = pc.CustomerID
        GROUP BY 
            pc.CustomerID
    )
    SELECT 
        tc.CustomerID,
        tc.TotalOrders,
        c.FirstName,
        c.LastName,
        c.Email
    FROM 
        TopCustomers tc
    JOIN 
        Customers c ON tc.CustomerID = c.CustomerID;
    """

    try:
        results = gridstore.query(query)
        while results.has_next():
            print(results.next())
    except griddb.GSException as e:
        print (f"Error Message: {e.get_message()}")

def main():
    try:
        gridstore = connect_to_griddb()
        top_customers(gridstore)

    except griddb.GSException as e:
        print(f"General GridDB Exception: {e}")

    finally:
        gridstore.close()

if __name__ == "__main__":
    main()

Error Message: Syntax error: unexpected ‘WITH’

I want to fetch top customer based on orders amount and value etc. This query works well with mssql server but giving error in grid dB:

Kindly guide me on how I can improve logic on query level or python application level so that I can get desired result. (Application-level logic in python will be highly appreciated but query level is also welcomed)