SQL join using substring in snowflake

Question

I am trying to run this query but it's taking too long because to join with my calling_zones table and also creating multiple records. for e.g if my terminating number is '9974552425' then i will get output with 4 duplicate records matching with calling zones '99','997','9974',''99745'.

Output required: I would like to only get the only match where my max length of terminating number matches with calling zone area code and replace this join ("ON") statement accordingly. because my query is taking maximum amount of time running this join. maybe if regex_substring or something can fast the query performance.

I would appreciate any help to resolve this problem.

SELECT
    a.gsmcallreferencenumber
    , a.CONNECT_DATETIME
    , a.TOTAL_CALL_DURATION_SEC
    , a.ORIGINATING_NUMBER
    , a.TERMINATING_NUMBER
    , cz.ZONE_AREA_CODE_PRICE
    , cz.ZONE_AREA_CODE_PRICE_CURRENCY
    , cz.ZONE_AREA_CODE
    , cz.ZONE_DESTINATION
    , ROUND(nvl(1.0 * (a.TOTAL_CALL_DURATION_SEC / 60) * cz.ZONE_AREA_CODE_PRICE, 0), 2) AS CALL_COST
FROM
    tablex  a
    LEFT JOIN CALLING_ZONES cz
        ON SUBSTR(A.TERMINATING_NUMBER, 1,length(cz.ZONE_AREA_CODE) ) = cz.ZONE_AREA_CODE
GROUP BY
    a.CONNECT_DATETIME
    , a.TOTAL_CALL_DURATION_SEC
    , a.ORIGINATING_NUMBER
    , a.TERMINATING_NUMBER
    , cz.ZONE_AREA_CODE_PRICE
    , cz.ZONE_AREA_CODE_PRICE_CURRENCY
    , cz.ZONE_AREA_CODE
    , cz.ZONE_DESTINATION;

sample data

Table a                       Table Calling Zone
a.Terminating number             zone_area_code 

9974552425                         5324
99526552425                        995
5324664892                         9952
3274639201                         99526
6473294465                         997 
                                   9974

current output with query mentioned

Output

Terminating_Number      Zone area code
9974552425                997
9974552425                9974
99526552425               995
99526552425               9952
99526552425               99526
5324664892                 5324
3274639201                 null
6473294465                 null

Desired Output:

9974552425                9974
99526552425               99526
5324664892                 5324
3274639201                 null
6473294465                 null

question from:https://stackoverflow.com/questions/65921588/sql-join-using-substring-in-snowflake

Answer 1

Waitting for answers