/*
Google SQL Interview Question

Find the top 3 most common letters across all the words from both the tables (ignore filename column).
Output the letter along with the number of occurrences and order records in descending order based on the number of occurrences.
*/
    
-- Schema Setup
CREATE TABLE google_file_store (contents VARCHAR(100), filename VARCHAR(255));

INSERT INTO google_file_store (contents, filename) VALUES ('This is a sample content with some words.', 'file1.txt'), ('Another file with more words and letters.', 'file2.txt'), ('Text for testing purposes with various characters.', 'file3.txt');

CREATE TABLE google_word_lists ( words1 VARCHAR(100), words2 VARCHAR(100));

INSERT INTO google_word_lists (words1, words2) VALUES ('apple banana cherry', 'dog elephant fox'), ('grape honeydew kiwi', 'lemon mango nectarine'), ('orange papaya quince', 'raspberry strawberry tangerine');

-- Solution
WITH CTE AS
(
WITH RECURSIVE number AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM number
    WHERE n < (SELECT MAX(LENGTH(contents)) AS max_len FROM google_file_store)
)
    SELECT
        UPPER(SUBSTRING(contents, n, 1)) AS letter
    FROM
        google_file_store,
        number
    WHERE
        SUBSTRING(contents, n, 1) REGEXP '[A-Za-z]'
)

,CTE2 AS (
WITH RECURSIVE number1 AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM number1
    WHERE n < (SELECT MAX(LENGTH(words1)) FROM google_word_lists)
)
    SELECT
        UPPER(SUBSTRING(words1, n, 1)) AS letter
    FROM
        google_word_lists,
        number1
    WHERE
        SUBSTRING(words1, n, 1) REGEXP '[A-Za-z]'
)
    
,CTE3 AS
(WITH RECURSIVE number2 AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM number2
    WHERE n < (SELECT MAX(LENGTH(words2)) FROM google_word_lists)
)
    SELECT
        UPPER(SUBSTRING(words2, n, 1)) AS letter
    FROM
        google_word_lists,
        number2
    WHERE
        SUBSTRING(words2, n, 1) REGEXP '[A-Za-z]'
)

,combined_letters AS(
SELECT
    letter
FROM CTE
UNION ALL
SELECT
    letter
FROM CTE2
UNION ALL
SELECT
    letter
FROM CTE3)
    
SELECT
    letter,
    COUNT(*) AS occurrences
FROM combined_letters
GROUP BY letter
ORDER BY occurrences DESC
LIMIT 3;