compare values and partial names

    0

    0

    Manuel Antunes

    The SELECT statement would return the following:

    c.first_name c.last_name c.credit_limit new_limit max(p.credit_limit)

    Elizabeth Smith $10,000.00 10,000.

    
    SELECT
      c.first_name,
      c.last_name,
      c.credit_limit AS "old_limit",
      max(p.credit_limit) AS "new_limit"
    FROM customers c, prospects p
    WHERE lower(p.full_name) LIKE '%' || lower(c.first_name) || '%'
    AND lower(p.full_name) LIKE '%' || lower(c.last_name) || '%'
    AND p.credit_limit > c.credit_limit
    GROUP BY c.first_name, c.last_name, c.credit_limit
    ORDER BY first_name;
    
    -------------------------------------
    -------------------------------------
    
    CREATE INDEX ON customers (lower(first_name || ' ' || last_name), lower(first_name || ',' || last_name));
    CREATE INDEX ON prospects (lower(full_name));
    
    SELECT a.first_name,
            a.last_name,
            a.credit_limit AS old_limit,
            max(b.credit_limit) AS new_limit
    FROM customers a JOIN prospects b
      ON lower(full_name) IN (
        lower(a.first_name || ' ' || a.last_name),
        lower(a.last_name || ', ' || a.first_name)
      )
    GROUP BY a.id
      HAVING max(b.credit_limit) > a.credit_limit
    ORDER BY first_name, last_name
    --------------------
    --------------------
    
    create extension pg_trgm;
    create index prospects_idx on prospects using gin(full_name gin_trgm_ops);
    
    
    SELECT c.first_name
          , c.last_name
          , c.credit_limit as old_limit
          , MAX(p.credit_limit) as new_limit
    FROM customers c JOIN prospects p
    ON p.full_name ILIKE CONCAT('%', c.first_name, '%')
    and p.full_name ILIKE CONCAT('%', c.last_name, '%')
    and p.credit_limit > c.credit_limit
    GROUP BY c.first_name, c.last_name, c.credit_limit
    ORDER BY c.first_name
    Codiga Logo
    Codiga Hub
    • Rulesets
    • Playground
    • Snippets
    • Cookbooks
    soc-2 icon

    We are SOC-2 Compliance Certified

    G2 high performer medal

    Codiga – All rights reserved 2022.