365 Days of Daily Coding: Day 95

It’s been quite sometime since I last posted. I’ve been occupied with lot of things and I am gaining back my normalcy and routine. It’s as though I am waking up from my long dreamlike state and truly becoming aware of my self and my reality.

I did two challenges in Codility and y’all, I did so badly. LOL! Kinda surprising but I don’t really feel that disappointed unlike before cause I know that test doesn’t define me. I think I kinda failed the interview (will update once the recruiter sends an email or not) so I am gonna just take it as a redirection. I have been praciticing all this while in HackerRank, leetcode and SQLpad and the challenges were nowhere close to what I took in Codility. It was totally on a different level, more of like the challenges in the Advanced SQL site that I have been working on recently. I guess I will just continue practicing. The funny thing is right after the test finished which was for 1.3 hrs, all the logic and solution came to me. Funny! That is how life is working for me I suppose.

Below is one of the test that I did. I had to find out the names of the participants who attended a conference for atleast 3 consecutive years. I attempted to solve the challenge right after the test ended and well, I got the below solution. This may or may not have solved all the test cases that codility measures the solution against but I am pretty confident about the below solution. I will post about the second test tomorrow and I am cringing right now at some of the silly mistakes I made on the second challenge.

WITH temp AS (
Select 'Lyna' AS Name, '6/1/2018' AS JoinDate FROM Dual Union All
Select 'Lyna' AS Name, '6/2/2018' AS JoinDate FROM Dual Union All
Select 'Lyna' AS Name, '6/3/2018' AS JoinDate FROM Dual Union All
Select 'Lyna' AS Name, '6/6/2018' AS JoinDate FROM Dual Union All
Select 'Jo'   AS Name, '6/1/2018' AS JoinDate FROM Dual Union All
Select 'Cami' AS Name, '6/1/2018' AS JoinDate FROM Dual Union All
Select 'Cami' AS Name, '6/2/2018' AS JoinDate FROM Dual Union All
Select 'Kate' AS Name, '6/2/2018' AS JoinDate FROM Dual Union All
Select 'Kate' AS Name, '6/4/2018' AS JoinDate FROM Dual Union All
Select 'Kate' AS Name, '6/5/2018' AS JoinDate FROM Dual Union All
Select 'Kate' AS Name, '6/6/2018' AS JoinDate FROM Dual 
), temp_2 AS (
SELECT DISTINCT Name FROM (
    SELECT Name, JoinDate, RANK() OVER(PARTITION BY Name ORDER BY JoinDate) AS rnk
    FROM temp)
    WHERE rnk >=3
    )

SELECT Name FROM(
SELECT Name, TO_DATE(JoinDate,'MM/DD/YYYY') - TO_DATE(prev_date_1,'MM/DD/YYYY') AS diff_1, TO_DATE(prev_date_1, 'MM/DD/YYYY') - TO_DATE(prev_date_2, 'MM/DD/YYYY') AS diff_2
FROM(
SELECT * FROM (
SELECT Name, JoinDate, prev_date_1, LAG(prev_date_1) OVER (PARTITION BY Name ORDER BY JoinDate) AS prev_date_2
FROM(
SELECT Name, JoinDate, LAG(JoinDate) OVER (PARTITION BY Name ORDER BY JoinDate) AS prev_date_1
FROM (
SELECT * FROM temp
WHERE Name IN (SELECT * FROM temp_2)
ORDER BY Name ASC, JoinDate ASC)))
WHERE prev_date_1 IS NOT NULL AND prev_date_2 IS NOT NULL))
WHERE diff_1 = 1 and diff_2 = 1