How can I rewrite this query by not having AS?

November 21, 2022

I’m completing a HackerRank challenge, but the documentation says I should not use the AS keyword:

I need to rewrite this query in MySQL so it doesn’t include the AS in WITH A AS, nor AS in SELECT...AS test

WITH A AS (
    SELECT DISTINCT
        MAX( LENGTH( customer_id ) ) AS test
    FROM
        orders

    UNION

    SELECT DISTINCT
        MIN( LENGTH( customer_id ) )
    FROM
        orders
)
SELECT
    test,
    LENGTH(test)
FROM
    A

>Solution :

The WITH clause is using for declare a VIEW, so you can rewrite it like below


SELECT
    test,
    LENGTH(test)
FROM
    (
 SELECT DISTINCT
        MAX( LENGTH( customer_id ) ) AS test
    FROM
        orders

    UNION

    SELECT DISTINCT
        MIN( LENGTH( customer_id ) )
    FROM
        orders)