Answer-first summary for fast verification
Answer: SELECT PageName, AVG(TimeSpent) AS AvgTimeSpent FROM WebsiteTraffic WHERE IPAddress = '192.168.1.1' GROUP BY PageName;
Option A is the correct answer because it efficiently filters the dataset for the specified IP address using the WHERE clause, which is optimal for performance with large datasets. It then groups the results by page name and calculates the average time spent on each page using the AVG() function, ensuring compliance with data privacy standards by aggregating data and not exposing individual visitor details. Options B and D incorrectly calculate the average, and Option C misuses the HAVING clause, which is intended for filtering groups, not individual records.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
As a Microsoft Fabric Analytics Engineer Associate, you are tasked with analyzing website traffic data stored in a Fabric lakehouse. The dataset includes columns for the visitor's IP address, the page they visited, and the time spent on the page. Your goal is to write a SQL query that calculates the average time spent on each page by visitors from a specific IP address, '192.168.1.1', and includes the page name in the results. Consider the following constraints: the solution must efficiently handle large datasets, comply with data privacy standards by not exposing individual visitor data, and be optimized for performance. Which of the following SQL queries meets all these requirements? (Choose one option.)
A
SELECT PageName, AVG(TimeSpent) AS AvgTimeSpent FROM WebsiteTraffic WHERE IPAddress = '192.168.1.1' GROUP BY PageName;
B
SELECT PageName, SUM(TimeSpent) / COUNT(*) AS AvgTimeSpent FROM WebsiteTraffic WHERE IPAddress = '192.168.1.1' GROUP BY PageName;
C
SELECT PageName, AVG(TimeSpent) AS AvgTimeSpent FROM WebsiteTraffic GROUP BY PageName HAVING IPAddress = '192.168.1.1';
D
SELECT PageName, SUM(TimeSpent) / COUNT(DISTINCT IPAddress) AS AvgTimeSpent FROM WebsiteTraffic WHERE IPAddress = '192.168.1.1' GROUP BY PageName;
No comments yet.