<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Manpreetsingh on Medium]]></title>
        <description><![CDATA[Stories by Manpreetsingh on Medium]]></description>
        <link>https://medium.com/@manpreetsinghmessi?source=rss-15aa12c0d7ea------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*uOqiw6cVqmlq819WFWmDdw.jpeg</url>
            <title>Stories by Manpreetsingh on Medium</title>
            <link>https://medium.com/@manpreetsinghmessi?source=rss-15aa12c0d7ea------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 04:30:13 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@manpreetsinghmessi/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[ETL on API :Store data from the Spotify API on the Cloud]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://medium.com/@manpreetsinghmessi/store-data-from-the-spotify-api-on-the-cloud-c45e1033b01c?source=rss-15aa12c0d7ea------2"><img src="https://cdn-images-1.medium.com/max/600/1*OCE_obQof9PtMq5G1G6l0A.png" width="600"></a></p><p class="medium-feed-link"><a href="https://medium.com/@manpreetsinghmessi/store-data-from-the-spotify-api-on-the-cloud-c45e1033b01c?source=rss-15aa12c0d7ea------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://medium.com/@manpreetsinghmessi/store-data-from-the-spotify-api-on-the-cloud-c45e1033b01c?source=rss-15aa12c0d7ea------2</link>
            <guid isPermaLink="false">https://medium.com/p/c45e1033b01c</guid>
            <category><![CDATA[etl-pipeline]]></category>
            <category><![CDATA[technology]]></category>
            <category><![CDATA[beginner]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Manpreetsingh]]></dc:creator>
            <pubDate>Fri, 05 Jul 2024 08:02:08 GMT</pubDate>
            <atom:updated>2024-07-27T00:18:59.517Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Spotify API’s using Azure Data Factory]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://medium.com/@manpreetsinghmessi/spotify-apis-using-azure-data-factory-dae008fd648c?source=rss-15aa12c0d7ea------2"><img src="https://cdn-images-1.medium.com/max/1354/1*smyGRFkTXwmMv-b-p1Fcmw.png" width="1354"></a></p><p class="medium-feed-snippet">In this article I am going to show you how to write an ADF pipeline to access data from Spotify API and later on you can play around or&#x2026;</p><p class="medium-feed-link"><a href="https://medium.com/@manpreetsinghmessi/spotify-apis-using-azure-data-factory-dae008fd648c?source=rss-15aa12c0d7ea------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://medium.com/@manpreetsinghmessi/spotify-apis-using-azure-data-factory-dae008fd648c?source=rss-15aa12c0d7ea------2</link>
            <guid isPermaLink="false">https://medium.com/p/dae008fd648c</guid>
            <category><![CDATA[data-engineer-api]]></category>
            <category><![CDATA[data-engineering-skills]]></category>
            <category><![CDATA[technology-trends]]></category>
            <category><![CDATA[spotify-api]]></category>
            <category><![CDATA[spotify-api-data]]></category>
            <dc:creator><![CDATA[Manpreetsingh]]></dc:creator>
            <pubDate>Sun, 30 Jun 2024 05:40:29 GMT</pubDate>
            <atom:updated>2024-06-30T05:40:29.513Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Real SQL Interview Question for the Data Engineer position]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://medium.com/@manpreetsinghmessi/real-sql-interview-question-for-the-data-engineer-position-34a885368987?source=rss-15aa12c0d7ea------2"><img src="https://cdn-images-1.medium.com/max/750/1*WSwvXq3QLSjns0dFCBGLlg.png" width="750"></a></p><p class="medium-feed-snippet">This question was asked by the interviewer to solve it live on tCoderbyte platform.</p><p class="medium-feed-link"><a href="https://medium.com/@manpreetsinghmessi/real-sql-interview-question-for-the-data-engineer-position-34a885368987?source=rss-15aa12c0d7ea------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://medium.com/@manpreetsinghmessi/real-sql-interview-question-for-the-data-engineer-position-34a885368987?source=rss-15aa12c0d7ea------2</link>
            <guid isPermaLink="false">https://medium.com/p/34a885368987</guid>
            <category><![CDATA[interview-questions]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Manpreetsingh]]></dc:creator>
            <pubDate>Thu, 28 Dec 2023 07:40:16 GMT</pubDate>
            <atom:updated>2023-12-28T07:40:16.989Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Real SQL Interview Question for the Data Analyst position]]></title>
            <link>https://medium.com/@manpreetsinghmessi/interview-question-data-analyst-dec-2023-149a852fa17e?source=rss-15aa12c0d7ea------2</link>
            <guid isPermaLink="false">https://medium.com/p/149a852fa17e</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[sql-interview-questions]]></category>
            <category><![CDATA[data-analyst]]></category>
            <category><![CDATA[interview-questions]]></category>
            <category><![CDATA[real-problem]]></category>
            <dc:creator><![CDATA[Manpreetsingh]]></dc:creator>
            <pubDate>Mon, 04 Dec 2023 03:49:17 GMT</pubDate>
            <atom:updated>2024-01-03T07:26:53.160Z</atom:updated>
            <content:encoded><![CDATA[<h3>Data Analyst Interview Question on Self Join</h3><p>My friend recently gave a Data Analyst SQL assessment and got stuck on this problem. This question was on self-join so I would rate this as an intermediate level SQL question.I gave this question a try and thought of sharing with all future DA’s .</p><p><strong>Problem :</strong> Find out the the id,first name and last name of the employees who have same experience as of the other employees and years.</p><p><strong>Output :</strong> Id of the matching employee , first and last name of the employee who have matched in experience.</p><p><strong>Sort :</strong> Sort by Id lowest and age by highest order.</p><p><strong>Table : employee_experience</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1002/1*x-2n4TJQu6z1_v1uD8j9xw.png" /><figcaption>employee_experience</figcaption></figure><p><strong>Solution:</strong></p><p>Now we ran a simple select query to view the rows, data type and overall count of rows.Before proceeding to solution we need to understand the ask , in simplified terms the id 100 with years as 10 with first name and last name Jack and Hopper respectively needs to be matched with employees of same years of experience. <em>So the joining will be based on years and join will be a self-join.</em></p><p>So self joining we will create two virtual tables with different aliases of the same table.</p><p>select e.*, ep.*</p><p>from <strong>employee_experience e</strong> <br><strong><em>inner join</em></strong><em> </em><strong>employee_experience ep </strong><br>on e.years = ep.years ;</p><p>So the above query will join the same table with itself and will get only records where years are matching.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*s4yWmEB0YjuzHwc7NlBi1g.png" /><figcaption>Self Join with match on years</figcaption></figure><p>Now above we see that Id 100 is matching with itself for years = 10 along with other Id’s 112,106,123,150 and so on. You must be thinking that’s it and we have the solution but not so fast. The test cases won’t allow it to be a piece of cake.</p><p>a. Firstly we would not like to see the same id 100 in the matched set as it is creating an extra record when we want only the matching years.</p><p>b. We only want to see the Matching Id in result set not the matched id ,meaning if I am matching years=10 for Id 100 then I want to see only Id 100 in result not the matching Id’s like 112,123 … etc.</p><p>So we would have to be meticulous in what columns go into the output and from which virtual table and the most important how to get rid of self matching Id’s.</p><p><strong>Modified query would look like below along with the sorting as asked.</strong></p><p><em>select e.id as MatchingId — </em><strong><em>Id of the employee whose years of experience we will look to match</em></strong></p><p><em>,ep.id as MatchedId, —</em><strong><em> Id of the employees which matched on years</em></strong></p><p><em>ep.first_name as FirstName, — First name of employees matched</em></p><p><em>ep.last_name as LastName, — Last name of employees matched</em></p><p><em>ep.years as YearsOfExperience</em></p><p><em>from employee_experience e</em></p><p><em>inner join employee_experience ep</em></p><p><em>on e.years=ep.years</em></p><p><em>where e.first_name != ep.first_name</em></p><p><strong><em>order by e.id,ep.years asc</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EuCRg5FuzwGCTz1b8Q5YJQ.png" /><figcaption>Final Result</figcaption></figure><p>So from above query we have removed the extra record by filtering on the first name ,meaning we don’t want to have same first name to be part of the result set.So Jack Hopper is not part of the result set but only the Id of Jack Hopper with years = 10 is part of the result set.</p><p>So the result query is below</p><p>select e.id as MatchingId ,</p><p>ep.first_name as FirstName,</p><p>ep.last_name as LastName</p><p>from employee_experience e</p><p>inner join employee_experience ep</p><p>on e.years=ep.years</p><p>where e.first_name != ep.first_name</p><p>order by e.id,ep.years asc</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-pwGB85bi6VhG1aXZaHy7Q.png" /><figcaption>Submitted Solution</figcaption></figure><p>Hope you liked this question and would be of help to your SQL knowledge and interviews in the future. <strong>To practice yourself I given below the insert statements.Try this question with different join or create more complex questions. If you have any other solution please feel free to comment it</strong>. <strong><em>Also If you liked this question please share with your friends and colleagues and kindly hit a clap or comment on the story.</em></strong></p><p><em>Cheers to all! and best wishes for the interviews.</em></p><p>insert into employee_experience values(100,’Jack’,’Hopper’,10,’Y’); <br>insert into employee_experience values( 112, ‘Andre’ ,’Hopper’, 10 ,’Y’);<br>insert into employee_experience values( 113, ‘Rick’ ,’Zelas’, 9 ,’Y’);<br>insert into employee_experience values( 106, ‘Pablo’ ,’Dugas’, 10 ,’Y’);<br>insert into employee_experience values( 111, ‘Kim’ ,’kaiser’, 8 ,’Y’);<br>insert into employee_experience values( 123, ‘Layun’ ,’Wen’, 10 ,’Y’);<br>insert into employee_experience values( 145, ‘Erika’ ,’Cooper’, 7 ,’Y’);<br>insert into employee_experience values( 150, ‘Jack’ ,’Russell’, 10 ,’Y’);<br>insert into employee_experience values( 105, ‘Emik’ ,’Salah’, 11 ,’Y’);<br>insert into employee_experience values( 135, ‘Vini’ ,’Junior’, 10 ,’Y’);<br>insert into employee_experience values( 135, ‘Leo’ ,’Matthew’, 11 ,’Y’);<br>insert into employee_experience values( 138, ‘Judy’ ,’Hopper’, 10 ,’Y’);<br>insert into employee_experience values( 151, ‘Carie’ ,’Saumer’, 7 ,’Y’);<br>insert into employee_experience values( 190, ‘Liam’ ,’Sean’, 10 ,’Y’);<br>insert into employee_experience values( 160, ‘Seri’ ,’Bong’, 8 ,’Y’);</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=149a852fa17e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL LEAD Window Function]]></title>
            <link>https://medium.com/@manpreetsinghmessi/sql-lead-window-function-a96a4a3a252d?source=rss-15aa12c0d7ea------2</link>
            <guid isPermaLink="false">https://medium.com/p/a96a4a3a252d</guid>
            <category><![CDATA[lead-function]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[window-functions]]></category>
            <category><![CDATA[analytics]]></category>
            <dc:creator><![CDATA[Manpreetsingh]]></dc:creator>
            <pubDate>Fri, 28 Apr 2023 05:59:39 GMT</pubDate>
            <atom:updated>2023-12-02T05:18:22.182Z</atom:updated>
            <content:encoded><![CDATA[<p>We will see how we can use the LEAD function for creating a condition to get the desired results.</p><p>Let’s understand the data and the problem.</p><ol><li>Data is shown as below ,with a table showing transactions by the users for products bought on a date.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/595/1*77ldLuw4qnjXBgLWjqD4ZQ.png" /><figcaption>Transactions Table</figcaption></figure><p>2. <strong>Now we want to see the number of users who have bought products after the first purchase and in future too but not on the same day</strong>.</p><p>3. So if we look at the data we see that like user_id purchased products in different years/days so this will be part of our count .</p><p>4. We can easily achieve by using the lead function to see the next purchase date of each user and always getting those users who have met this condition. Let’s write the lead function on create date.</p><p>5. <strong>Lead function can take three arguments LEAD(return_value ,offset [,default])</strong>.In this case we just want to get the next purchase date irrespective of the when the purchase was made. So I will use only <strong>LEAD(created_at) over (Partition by user_id order by created_at ). Here I am using the default order by which is ascending as we want to see the first purchase date and then the very next .</strong></p><p>6. So now just to show the sample output of lead function we get below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/587/1*cMtnMHkQDl5N5tcIt-wLJw.png" /><figcaption>LEAD next purchase dates</figcaption></figure><p>So now we don’t need users who didn’t purchase anything next time and the ones who purchased on the same date like user_id 21.</p><p>7. So final query will look like below and then we will put this query into a CTE and then get distinct count of user_id since we only need distinct users not the number of times by user , you can add that also using the same CTE.</p><p><strong>with cte1 as (select *,<br>lead(created_at) over (partition by user_id order by created_at ) as nextpurchase<br>from transactions)<br>select count(distinct user_id) as num_users, count(user_id) as total_orders from cte1<br>where nextpurchase &gt; created_at</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a96a4a3a252d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Use CASE statement to PIVOT data in SQL]]></title>
            <link>https://medium.com/@manpreetsinghmessi/perform-pivot-in-sql-without-pivot-function-f3e14e407a4c?source=rss-15aa12c0d7ea------2</link>
            <guid isPermaLink="false">https://medium.com/p/f3e14e407a4c</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[beginner]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[case-statement]]></category>
            <category><![CDATA[pivot]]></category>
            <dc:creator><![CDATA[Manpreetsingh]]></dc:creator>
            <pubDate>Mon, 24 Apr 2023 06:27:43 GMT</pubDate>
            <atom:updated>2023-04-26T06:36:24.236Z</atom:updated>
            <content:encoded><![CDATA[<p>Perform PIVOT in SQL without PIVOT function</p><p>In this simple query we would see how to create a PIVOT output (converting a row to column). We will be using the case statement depending on number of columns we need in the output.</p><p>We have a sample table called Scores and we want to see the output by students and their scores in different exams.</p><p>Input :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/402/1*Vr7CWKsmU7yOd7Xpb6KvLQ.png" /><figcaption>Input</figcaption></figure><p>Expected Output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/452/1*cZDkzd3FfFeLHk4k6LuUoQ.png" /><figcaption>Output</figcaption></figure><p>Final Query:</p><p>SELECT student_name,max(case when exam_id=1 then score end) as exam_1,</p><p>max(case when exam_id=2 then score end )as exam_2,</p><p>max(case when exam_id=3 then score end) as exam_3,</p><p>max(case when exam_id=4 then score end) as exam_4 FROM exam_scores</p><p>group by student_name;</p><p>Query Buildup:</p><ol><li>First we see how many unique exams do we have and that count will be our number of case statements.</li><li>Then we see on which column we can do the aggregation by id or name depending on your input table ,in this case by name.</li><li>Now we write the case statement with just the exam_id where we say when exam_id =’exam_1&#39; then give me score of that exam otherwise end of the statement because we don’t want nulls. Similarly do the same for other exam id’s.</li><li>So when we ran the case statements only we would get the nulls too and we need to do some aggregation, so we can use max,min,avg functions because sum and count won’t be helpful for this case as we just want to convert column to row.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/591/1*JHvv3R86t-2BX8g-9ALGUQ.png" /><figcaption>Output just with CASE statement</figcaption></figure><p>5. So now we aggregate this query using max() function and then added the student name in select statement along with in group by clause. From this we get the desired output.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/558/1*FFC6vymqxQ4SesjB1_KwIA.png" /><figcaption>Final Query</figcaption></figure><p>Points to consider:</p><p>This will be a very expensive query as the table will be traversed through each case statement and if you have large number of columns required use the PIVOT function for better performance.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f3e14e407a4c" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>