SQL Intermediate Interview questions

Question: You have a table that records website traffic. The table contains website name (multiple websites), page name, IP address and UTC date time. What would be the query to show all websites visited in the last 30 days with total number or visits, total number if unique page view and total number of unique visitors (using IP Address)?

Answer: This test is mainly about good understanding of aggregate functions and date time. In this we need to group by Website, Filter data using datediff but the trick in here is to use correct time zone. If I want to do that using UTC time than I could use GetUTCDate() in sql server and the final answer related to calculated fields using aggregate functions that I will list on separate lines below:

TotalNumberOfClicks = Count(*) 'nothing special here

TotalUniqueVisitors = Count(distinct Ipaddress) ' we count ipaddress fields but only unique ip addresses. The next field should be in here but as it is more complicated I put it as third field.

TotalNumberOfUniquePageViews = Count(distinct PageName+IPAddress) 'This one is tricky to get unique pageview we need to count all visits but per page but only for unique IP address. So I combined pagename with ipaddress to counted unique values. Just to explain one page could receive 3 vists from 2 unique visits and another page could receive one visit from ip that visited previous page so Unique IP is 2, PageView is 3 (1 visitor 2 pages and 1 visitor 1 page) and visits is 4

Question: How to display top 5 employees with the higest number of sales (total) and display position as a field. Note that if both of employees have the same total sales values they should receive the same position, in other words Top 5 employees might return more than 5 employees.

Answer: Microsoft introduced in SQL Server 2005 ranking function and it is ideal to solve this query. RANK() function can be used to do that, DENSE_Rank() can also be used. Actually the question is ambiguous because if your two top employees have the same total sales which position should the third employee get 2 (Dense_Rank() function) or 3 (Rank() Function)? In order to filter the query Common Table Expression (CTE) can be used or query can be put inside FROM using brackets ().

Now that we covered basic and intermediate questions let's continue with more complicate ones. These questions and answers are suitable for experienced candidates: