LC 585 & LC 1484 & LC 1527 & LC 1667 & LC 2118 & LC 2199

The following intro-level SQL problems are about manipulating ‘string’ by GROUP_CONCAT, CONCAT, LEFT, RIGHT, SUBSTRING, UPPER, LOWER, and ‘regular expression (REGEX)’ by RLIKE or REGEXP.

LeetCode 585

Investments in 2016 (Medium) [link]

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM insurance
WHERE tiv_2015 IN (
    SELECT tiv_2015
    FROM insurance
    GROUP BY tiv_2015
    HAVING COUNT(tiv_2015) >= 2
) AND
    CONCAT(lat,lon) IN (
    SELECT CONCAT(lat,lon)
    FROM insurance
    GROUP BY lat,lon
    HAVING COUNT(*) = 1
);

LeetCode 1484

Group Sold Products by The Date (Easy) [link]

SELECT 
    sell_date, 
    COUNT(DISTINCT product) AS num_sold,
    GROUP_CONCAT(DISTINCT product) AS products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date;

Note that DISTINCT would order the product lexicographically.

LeetCode 1527

Patients with a Condition (Easy) [link]

SELECT patient_id, patient_name, conditions
FROM Patients
WHERE conditions rlike "^DIAB1|\\sDIAB1";

SELECT patient_id, patient_name, conditions
FROM Patients
WHERE conditions REGEXP "^DIAB1|\\sDIAB1";

LeetCode 1667

Fix Names in a Table (Easy) [link]

SELECT user_id, 
    CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2))) AS name
FROM Users
ORDER BY user_id;

SELECT user_id, 
    CONCAT(UPPER(LEFT(name, 1)), LOWER(RIGHT(name, LENGTH(name)-1))) AS name
FROM Users
ORDER BY user_id;

LeetCode 2118

Build the Equation (Hard) [link]

Idea: Use GROUP_CONCAT to concat values in a column.

with tmp as(
    SELECT power,
        CASE power 
            WHEN 0 THEN IF(factor < 0, factor, CONCAT('+', factor))
            WHEN 1 THEN CONCAT(IF(factor < 0, factor, CONCAT('+', factor)), 'X') 
            ELSE CONCAT(IF(factor < 0, factor, CONCAT('+',factor)), "X^", power) 
            END AS equation
    FROM Terms    
)

SELECT CONCAT(GROUP_CONCAT(equation ORDER BY power DESC SEPARATOR ''), '=0') AS equation
FROM tmp;

LeetCode 2199

Finding the Topic of Each Post (Hard) [link]

The INSTR() function returns the position of the first occurrence of a string in another string. It’s a case-insensitive search.

GROUP_CONCAT to concat strings by column by group.

WITH t AS (
    SELECT DISTINCT post_id,topic_id 
    FROM Keywords,Posts 
    WHERE INSTR(CONCAT(' ',content,' '),CONCAT(' ',word,' '))>0
)

SELECT post_id, GROUP_CONCAT(topic_id ORDER BY topic_id,'') topic 
FROM t 
GROUP BY post_id
UNION
SELECT P.post_id,'Ambiguous!' 
FROM Posts P 
LEFT JOIN t 
ON P.post_id = t.post_id 
GROUP BY P.post_id 
HAVING COUNT(t.topic_id)=0;