/*
KPMG SQL Interview Question

You are given a table, support_, that stores information about employees, their office locations, email addresses, the floors they visit, and the resources they use.

Your task is to write an SQL query to generate a summary report for each unique name with the following details:
Total_Visit: The total number of entries (visits) for each name.
Most_Visited: The floor that the user has visited the most.
Resources_Used: A comma-separated list of distinct resources used by the user.
*/

-- Schema Setup
CREATE TABLE support_ (
    name VARCHAR(20),
    address VARCHAR(20),
    email VARCHAR(50),
    floor_ INT,
    resources VARCHAR(20)
);

INSERT INTO support_ (name, address, email, floor_, resources) VALUES
('A', 'BANGALORE', 'A@GMAIL.COM', 1, 'CPU'),
('A', 'BANGALORE', 'A1@GMAIL.COM', 1, 'CPU'),
('A', 'BANGALORE', 'A2@GMAIL.COM', 2, 'DESKTOP'),
('B', 'BANGALORE', 'B@GMAIL.COM', 2, 'DESKTOP'),
('B', 'BANGALORE', 'B1@GMAIL.COM', 2, 'DESKTOP'),
('B', 'BANGALORE', 'B2@GMAIL.COM', 1, 'MONITOR');

-- Solution
WITH floor_ranking AS
(
 SELECT
    name,
    floor_,
    DENSE_RANK() OVER(PARTITION BY name ORDER BY COUNT(floor_) DESC) AS rnk
 FROM support_
 GROUP BY name,floor_
)
,required_data AS
(
 SELECT
    name,
    COUNT(address) AS Total_Visit,
    GROUP_CONCAT(DISTINCT resources) AS resources_used
 FROM support_
 GROUP BY name
)
SELECT
    d.Name,
    d.Total_Visit,
    r.floor_ AS Most_Visited,
    d.Resources_Used
FROM required_data d 
JOIN floor_ranking r
ON r.name = d.name
AND r.rnk = 1;