mysql - SHAHRUKH KHAN question SQL query on IMDB data set

Question

Q; The Shahrukh number of an actor is the length of the shortest path between the actor and Shahrukh Khan in the "co-acting" graph. That is, Shahrukh Khan has Shahrukh number 0; all actors who acted in the same film as Shahrukh have Shahrukh number 1; all actors who acted in the same film as some actor with Shahrukh number 1 have Shahrukh number 2, etc. Return all actors whose Shahrukh number is 2

query9 ="""WITH SHAHRUKH_0
AS
    (
        SELECT
            TRIM(P.PID) PID
        FROM
            Person P
        WHERE
            Trim(P.NAME) = "Shah Rukh Khan"
    )
, SHAHRUKH_1_MOVIES
AS
    (
        SELECT DISTINCT
            TRIM(MC.MID) MID
            , S0.PID
        FROM
            M_Cast MC
            , SHAHRUKH_0 S0
        WHERE
            TRIM(MC.PID) = S0.PID
    )
, SHAHRUKH_1_ACTORS
AS
    (
        SELECT DISTINCT
            TRIM(MC.PID) PID
        FROM
            M_Cast MC
            , SHAHRUKH_1_MOVIES S1M
        WHERE
            TRIM(MC.MID) = S1M.MID
            AND TRIM(MC.PID) <> S1M.PID
    )
, SHAHRUKH_2_MOVIES
AS
    (
        SELECT DISTINCT
            TRIM(MC.MID) MID
            , S1A.PID
        FROM
            M_Cast MC
            , SHAHRUKH_1_ACTORS S1A
        WHERE
            TRIM(MC.PID) = S1A.PID
    )
SELECT DISTINCT
    TRIM(P.Name) ACTOR_NAME
FROM
    Person P
    , M_Cast MC
    , SHAHRUKH_2_MOVIES S2M
WHERE
    TRIM(MC.PID) = TRIM(P.PID)
    AND TRIM(MC.MID) = S2M.MID
    AND TRIM(MC.PID) != S2M.PID;"""

Total s2 actors are 25698 but i am getting 26521. please suggest.

question from:https://stackoverflow.com/questions/65886735/shahrukh-khan-question-sql-query-on-imdb-data-set

Answer 1

Waitting for answers