Visit here for our full Databricks Certified Data Analyst Associate exam dumps and practice test questions.
Question 31
An analyst needs to create a visualization showing sales trends over time with a forecast line extending into future months. Which Databricks SQL visualization type supports trend forecasting?
A) Bar chart
B) Line chart with forecast
C) Pie chart
D) Scatter plot
Answer: B
Explanation:
Visualizing trends and forecasts helps stakeholders understand historical patterns and anticipate future performance. Different visualization types serve different analytical purposes with specific capabilities for time-series analysis and forecasting.
Line chart with forecast in Databricks SQL provides built-in forecasting capabilities for time-series data. When creating line charts with date or timestamp axes, Databricks SQL offers options to enable forecasting that extends trend lines beyond the historical data range. The forecasting algorithm analyzes patterns in historical data and projects future values with confidence intervals shown as shaded regions around the forecast line.
Bar chart displays categorical or discrete data as rectangular bars with heights representing values. While bar charts can show time-series data with time periods as categories, they do not provide built-in forecasting functionality. Bar charts are better suited for comparing discrete values rather than showing continuous trends.
Pie chart displays proportions of a whole showing how individual parts contribute to the total. Pie charts are inappropriate for time-series data and cannot show trends or forecasts. They work best for displaying composition at a single point in time.
Scatter plot displays individual data points with two numeric dimensions showing relationships or correlations between variables. While scatter plots can include time as one dimension, they do not provide automated trend forecasting. Scatter plots show individual observations rather than aggregated trends.
Line chart with forecast implementation involves creating queries that return time-series data with date or timestamp columns and numeric measure columns, selecting line chart visualization type in Databricks SQL, configuring the X-axis to use the time column, and enabling forecast options in the chart settings.
Forecast settings allow specifying the forecast period determining how far into the future projections extend, and confidence interval width controlling the uncertainty range displayed. The visualization automatically calculates forecasts using appropriate time-series algorithms considering seasonality and trends in the data.
Multiple series can be displayed on the same chart with individual forecasts for each series enabling comparison of projected trends across different product lines, regions, or customer segments. Interactive features allow hovering over forecast lines to see specific predicted values with confidence ranges.
Question 32
A data analyst needs to schedule a Databricks SQL query to run automatically every morning at 6 AM and send results via email. Which feature provides query scheduling?
A) Manual query execution only
B) Databricks SQL Alerts with schedule
C) Query snippets
D) SQL warehouse configuration
Answer: B
Explanation:
Automating recurring analytics workflows eliminates manual effort and ensures stakeholders receive timely information. Databricks SQL provides scheduling capabilities for queries and alerts enabling automated data delivery.
Databricks SQL Alerts with schedule enables automated query execution on defined schedules with notification capabilities. Alerts can be configured to run queries at specified intervals such as daily at specific times, and send results or notifications via email when conditions are met or regardless of results. This feature automates routine reporting eliminating the need for manual query execution.
Manual query execution only requires analysts to manually run queries each time results are needed. This approach is time-consuming, error-prone, and does not scale for recurring analytics needs. Scheduling capabilities are essential for operational analytics.
Query snippets are reusable SQL code fragments that can be inserted into queries to standardize common logic like date filters or aggregations. While snippets improve query consistency and development efficiency, they do not provide scheduling or automation capabilities.
SQL warehouse configuration controls compute resources that execute queries including size, auto-stop behavior, and scaling properties. While warehouses must be running to execute scheduled queries, warehouse configuration itself does not provide query scheduling functionality.
Alert scheduling implementation involves creating a query that returns the data or metrics to monitor, creating an alert based on that query, configuring schedule settings specifying frequency such as daily, weekly, or custom intervals and execution time, and configuring destinations including email addresses for result delivery.
Alerts support two primary modes including conditional alerts that send notifications only when specific thresholds are exceeded or conditions met, and scheduled query results that send outputs regardless of values functioning as scheduled reports. The second mode enables daily morning reports with complete query results.
Email notifications can include result summaries directly in the message body or attach full result sets as CSV files. Multiple recipients can be configured ensuring relevant stakeholders receive automated updates. Alert history tracking shows execution times, success status, and delivered notifications.
Question 33
An analyst needs to create a dashboard that filters multiple visualizations simultaneously when a user selects a region from a dropdown. Which Databricks SQL feature enables interactive filtering?
A) Dashboard filters with parameter mapping
B) Separate queries for each region
C) Hard-coded WHERE clauses
D) Manual query editing
Answer: A
Explanation:
Interactive dashboards empower users to explore data dynamically without modifying queries. Dashboard filters provide user-friendly interfaces for adjusting analysis scope affecting multiple visualizations simultaneously.
Dashboard filters with parameter mapping enable interactive filtering across multiple visualizations on a dashboard. Filters are defined at the dashboard level and linked to query parameters used in underlying queries. When users change filter selections, all visualizations based on queries with mapped parameters automatically update showing filtered results. This creates cohesive interactive experiences where single filter selections affect entire dashboards.
Separate queries for each region would require creating and maintaining individual queries and visualizations for every possible filter value. This approach is unmaintainable as the number of possible filter values grows and provides poor user experience requiring separate dashboard views for different scenarios.
Hard-coded WHERE clauses in queries fix filter values requiring query modification to analyze different subsets. Hard-coded filters eliminate flexibility and require analyst intervention for each analysis variation. Dynamic filtering through parameters is far more efficient.
Manual query editing by end users is impractical for business users without SQL knowledge. Requiring users to modify queries to change filter values creates friction and limits dashboard accessibility. Dashboard filters provide user-friendly interfaces abstracting query complexity.
Dashboard filter implementation involves defining query parameters in SQL queries using double curly brace syntax with parameter names, creating dashboard filters that specify the parameter name, display label, and filter type such as dropdown, date range, or text input, and configuring filter options including possible values from query results or static lists.
Parameters in queries are replaced with filter values when visualizations render. Multiple queries can reference the same parameter enabling consistent filtering across related visualizations. Default parameter values ensure dashboards display meaningful data before users interact with filters.
Advanced filter configurations include cascading filters where one filter’s options depend on another filter’s selection, multi-select filters allowing users to choose multiple values simultaneously, and filter dependencies that automatically update related filters based on selections.
Question 34
A data analyst needs to join a sales table with a products table to add product names to sales records. Which SQL join type returns all sales records even if matching product information is missing?
A) INNER JOIN
B) LEFT OUTER JOIN
C) RIGHT OUTER JOIN
D) CROSS JOIN
Answer: B
Explanation:
Join operations combine data from multiple tables based on related columns. Different join types determine which records are included in results when matches between tables do not exist for all records.
LEFT OUTER JOIN returns all records from the left table and matching records from the right table. When no match exists in the right table, NULL values appear for right table columns. For sales and products tables where sales is the left table, LEFT JOIN ensures all sales records appear in results even if product IDs reference non-existent products. This preserves complete sales data while adding product information where available.
INNER JOIN returns only records where matches exist in both tables. Sales records without corresponding product entries would be excluded from results. While INNER JOIN ensures data quality by eliminating orphaned records, it risks losing sales data when product information is incomplete.
RIGHT OUTER JOIN returns all records from the right table and matching records from the left table. Using RIGHT JOIN with sales on the left and products on the right would return all products including those without sales, which does not meet the requirement of returning all sales records.
CROSS JOIN creates a cartesian product returning all possible combinations of rows from both tables regardless of any relationship. CROSS JOIN does not use join conditions and produces massive result sets inappropriate for typical table joining scenarios. It is rarely used outside specific analytical needs.
LEFT OUTER JOIN implementation uses standard SQL syntax with the sales table specified first, LEFT OUTER JOIN or LEFT JOIN keyword, the products table, and ON clause specifying the join condition typically matching foreign key to primary key. The query returns all sales records with product names populated where matches exist and NULL where products are missing.
When analyzing results, NULL values in product columns indicate data quality issues where sales reference non-existent products. These situations require investigation to determine whether product records were deleted, IDs were entered incorrectly, or referential integrity constraints need strengthening.
Best practices include using LEFT JOIN when preserving all records from the primary entity is essential, adding WHERE clauses to filter NULL values when only matched records are needed, and considering data quality implications when unmatched records appear in results.
Question 35
An analyst needs to calculate the percentage of total sales that each product category represents. Which SQL technique provides this calculation?
A) Simple GROUP BY with COUNT
B) Window function with SUM OVER
C) UNION of multiple queries
D) HAVING clause only
Answer: B
Explanation:
Calculating percentages of totals requires both individual group values and overall totals. Window functions enable computing aggregates over different scopes within the same query without requiring separate calculations or self-joins.
Window function with SUM OVER computes percentages of total by calculating both category-level sales and overall total sales in a single query. The window function SUM with OVER clause and empty parentheses calculates total across all rows while regular GROUP BY computes category subtotals. Dividing category sales by total sales and multiplying by 100 yields percentage contributions.
Simple GROUP BY with COUNT groups records and counts or sums within groups but cannot simultaneously access the overall total needed for percentage calculation. GROUP BY alone requires subsequent calculations or subqueries to compute percentages requiring more complex queries.
UNION combines results from multiple queries stacking rows vertically. While UNION could theoretically combine category subtotals with a separate total calculation, this approach is inefficient and complex compared to window functions. UNION does not provide the analytical capability window functions offer.
HAVING clause filters grouped results based on aggregate conditions. HAVING works with GROUP BY to filter groups but does not help calculate percentages relative to overall totals. HAVING filters results rather than computing comparative metrics.
Window function implementation for percentage calculations uses GROUP BY to compute category-level sales sums, divides each category sum by a window function that computes total sales across all categories, and multiplies by 100 for percentage format. The window function uses SUM OVER with empty parentheses or PARTITION BY specifying the entire result set.
The query returns one row per category with category name, category sales amount, total sales from the window function, and calculated percentage. Results show how each category contributes to overall sales enabling comparative analysis.
Window functions provide flexibility for various analytical calculations including running totals that accumulate values across ordered rows, ranking that assigns positions based on values, and moving averages that smooth time-series data. Understanding window functions expands analytical capabilities significantly.
Question 36
A data analyst needs to create a reusable query that accepts a date range as input to filter results. Which Databricks SQL feature enables parameterized queries?
A) Views
B) Query parameters
C) Temporary tables
D) CTAS statements
Answer: B
Explanation:
Reusable queries that accept inputs eliminate duplication and enable flexible analysis. Query parameters provide mechanisms for passing values into queries dynamically making queries adaptable to different analytical needs.
Query parameters in Databricks SQL enable creating queries with input variables that can be specified when the query executes. Parameters are defined using double curly brace syntax in SQL queries and assigned values through the query interface or dashboard filters. For date range filtering, parameters accept start and end dates that are substituted into WHERE clauses when the query runs.
Views are saved SELECT statements that encapsulate query logic providing reusable definitions. While views improve maintainability and provide abstraction, they do not accept runtime parameters. Views execute with fixed logic each time they are queried.
Temporary tables store intermediate results during query execution providing staging areas for multi-step transformations. Temporary tables help break complex queries into manageable pieces but do not provide parameterization. They are session-specific storage mechanisms.
CTAS statements meaning CREATE TABLE AS SELECT create new tables populated with query results. CTAS is useful for materializing query outputs but does not provide parameter functionality. CTAS creates static tables rather than parameterized queries.
Query parameter implementation involves defining parameters in queries using syntax like WHERE date BETWEEN and with parameter names in double curly braces, specifying parameter types such as date, number, or text, providing default values that apply when parameters are not explicitly set, and configuring parameter widgets in the query interface where users input values.
Parameters support various data types including text for string inputs, number for numeric values, date and datetime for temporal filtering, and dropdown or query-based dropdowns that present users with selection lists. Query-based dropdowns execute separate queries to populate options dynamically.
When queries with parameters are added to dashboards, parameters can be mapped to dashboard filters enabling users to control multiple visualizations through unified filter interfaces. This creates powerful interactive analytics where parameter changes instantly update all dependent visualizations.
Question 37
An analyst needs to identify duplicate records in a customer table where the same email address appears multiple times. Which SQL approach detects duplicates?
A) SELECT DISTINCT
B) GROUP BY with HAVING COUNT > 1
C) UNION ALL
D) CROSS JOIN
Answer: B
Explanation:
Data quality analysis requires identifying duplicate records that violate uniqueness constraints. SQL aggregation with filtering provides efficient methods for detecting records that appear multiple times based on identifying columns.
GROUP BY with HAVING COUNT greater than 1 identifies duplicate values by grouping records by the identifying column such as email address, counting records in each group, and filtering to show only groups with more than one record. This query returns the duplicate values and how many times each appears enabling analysts to assess duplication extent and identify specific problematic records.
SELECT DISTINCT removes duplicate rows from query results returning only unique combinations. While DISTINCT eliminates duplicates in output, it does not identify which values are duplicated or how many duplicates exist. DISTINCT is for deduplication rather than duplicate detection.
UNION ALL combines result sets from multiple queries including all rows even duplicates. UNION without ALL removes duplicates from the combined results. Neither operation helps identify duplicates within a single table. UNION operations combine separate datasets rather than analyzing single table contents.
CROSS JOIN creates cartesian products combining every row from one table with every row from another. CROSS JOIN has no relevance to duplicate detection which examines single tables. It is used for generating combinations not identifying duplicates.
Duplicate detection implementation groups by the identifying column or columns that should be unique, uses COUNT aggregate function to count records in each group, applies HAVING clause to filter groups to those with counts exceeding one, and optionally selects the count to show duplication frequency.
Extended versions of duplicate detection queries can retrieve full duplicate records by joining back to the original table using the duplicate values identified by the GROUP BY query. This shows all columns for duplicate records enabling analysts to determine which records to keep and which to remove.
Data cleansing workflows use duplicate detection to identify problems, analyze duplicate records to determine resolution strategies such as keeping most recent records or merging information, and implement deduplication logic using DELETE or INSERT INTO new tables with SELECT DISTINCT or ROW_NUMBER window functions for more sophisticated deduplication.
Question 38
A data analyst needs to convert a timestamp column to show only the date portion removing time information. Which SQL function extracts the date?
A) CAST
B) DATE
C) TO_DATE or CAST to DATE
D) SUBSTRING
Answer: C
Explanation:
Date and time manipulation is common in data analysis requiring extraction of date components, formatting conversions, and arithmetic operations. SQL provides functions for converting between timestamp and date types.
TO_DATE or CAST to DATE converts timestamp values to date values removing time components. TO_DATE function explicitly converts strings or timestamps to dates while CAST provides general type conversion functionality. Both approaches extract just the date portion discarding hours, minutes, and seconds which is useful for grouping data by day or filtering by date ranges.
CAST alone without specifying target data type does not accomplish the conversion. CAST must specify the target type as DATE to perform the required conversion. The function syntax includes CAST with the timestamp column AS DATE.
DATE function exists in some SQL dialects for creating date values or extracting date components but Databricks SQL uses TO_DATE or CAST for converting timestamps to dates. Function availability varies across SQL implementations.
SUBSTRING extracts portions of strings based on position but is not appropriate for timestamp conversion. While timestamps can be represented as strings and SUBSTRING could extract date portions, this approach is fragile and does not handle varying formats. Proper date functions provide robust type-safe conversions.
Date extraction implementation uses either TO_DATE function with the timestamp column as argument or CAST function with timestamp column CAST AS DATE syntax. Both produce date values that can be used in GROUP BY clauses for daily aggregations or WHERE clauses for date range filtering.
Additional date manipulation includes extracting specific components like year, month, or day using YEAR, MONTH, and DAY functions, performing date arithmetic using DATEADD for adding intervals or DATEDIFF for calculating differences, and formatting dates for display using DATE_FORMAT function.
Common patterns include grouping sales by date to analyze daily trends, filtering to recent time periods using date comparisons, and computing metrics over rolling windows using date arithmetic. Understanding date functions is essential for time-series analysis and reporting.
Question 39
An analyst needs to create a visualization showing the relationship between advertising spend and sales revenue for different products. Which visualization type best shows correlation between two numeric variables?
A) Bar chart
B) Scatter plot
C) Pie chart
D) Line chart
Answer: B
Explanation:
Different visualization types serve different analytical purposes. Choosing appropriate visualizations based on data characteristics and analytical questions ensures insights are effectively communicated.
Scatter plot displays the relationship between two numeric variables by plotting individual data points with one variable on the X-axis and another on the Y-axis. Each point represents one observation such as a product with its advertising spend and sales revenue. Scatter plots reveal correlations showing whether variables tend to increase together, move inversely, or show no relationship. Clusters, outliers, and patterns become visible through scatter plot visualization.
Bar chart displays values for categorical variables using bar height to represent quantities. While bar charts effectively compare values across categories, they do not show relationships between two numeric variables. Each bar represents a single value rather than a coordinate pair.
Pie chart shows proportional composition displaying how parts contribute to a whole. Pie charts are inappropriate for showing correlations between variables. They represent composition at a single point rather than relationships between multiple dimensions.
Line chart displays trends over time or ordered sequences connecting data points with lines. While line charts can show how two variables change together if time is shared on X-axis, scatter plots more directly reveal correlations by plotting variables against each other without time ordering.
Scatter plot implementation in Databricks SQL involves creating queries that return two numeric columns along with optional grouping columns for color coding, selecting scatter plot visualization type, configuring X-axis to one numeric column such as advertising spend, and configuring Y-axis to the other numeric column such as sales revenue.
Additional configuration options include using color to represent categories showing different products or regions as different colored point sets, sizing points based on a third variable creating bubble charts, and adding trend lines that fit linear or polynomial curves showing overall relationships.
Analysts interpret scatter plots by examining point patterns including positive correlation where both variables increase together, negative correlation where one increases as the other decreases, no correlation where points show random scatter, and non-linear relationships where patterns follow curves rather than straight lines.
Question 40
A data analyst needs to find the top 5 customers by total purchase amount from a sales table. Which SQL clause limits results to the top records?
A) WHERE
B) GROUP BY
C) LIMIT with ORDER BY
D) HAVING
Answer: C
Explanation:
Identifying top performers or highest values is common in analytics. SQL provides clauses for sorting results and limiting output to specified numbers of records enabling top-N queries.
LIMIT with ORDER BY retrieves the top N records by first sorting results according to specified criteria and then returning only the first N rows. For finding top customers by purchase amount, the query groups sales by customer, sums purchase amounts, orders by the sum in descending order, and limits to 5 records returning the five customers with highest totals.
WHERE clause filters records based on conditions before aggregation. WHERE is useful for restricting which records are included in analysis but does not limit output to top N results. WHERE filters by conditions not by rank or position.
GROUP BY groups records by specified columns enabling aggregate calculations like SUM, COUNT, or AVG within groups. GROUP BY is necessary for computing customer totals but does not limit results to top records. GROUP BY is used with LIMIT to achieve top N results.
HAVING clause filters grouped results based on aggregate conditions similar to WHERE for row-level filtering. HAVING could filter to customers with totals exceeding a threshold but cannot limit to exactly top 5 regardless of threshold. HAVING filters by conditions not by count.
Top N query implementation groups sales by customer ID or name, uses SUM aggregate function to calculate total purchase amounts per customer, orders results by the sum in descending order placing highest values first, and applies LIMIT 5 to return only the first five records.
The complete query structure combines multiple clauses: SELECT with customer identifier and SUM, FROM specifying the table, GROUP BY the customer column, ORDER BY the aggregate in descending order, and LIMIT with the desired count. This pattern applies broadly to top N analyses.
Variations include finding bottom N by using ascending order instead of descending, retrieving top N per category using window functions with RANK or ROW_NUMBER partitioned by category, and including ties by using window functions instead of LIMIT when multiple records have identical values.
Question 41
An analyst needs to create a calculated field in a Databricks SQL query that categorizes sales amounts as High, Medium, or Low based on value ranges. Which SQL construct provides conditional logic?
A) WHERE clause
B) CASE expression
C) GROUP BY
D) JOIN
Answer: B
Explanation:
Creating derived fields with conditional logic is essential for data categorization and bucketing. SQL provides expressions for implementing if-then-else logic within SELECT statements.
CASE expression implements conditional logic returning different values based on conditions similar to if-then-else statements in programming languages. CASE evaluates conditions in order returning the result associated with the first true condition or a default ELSE value if no conditions match. For categorizing sales amounts, CASE checks whether amounts exceed high thresholds returning High, fall below low thresholds returning Low, or fall between returning Medium.
WHERE clause filters records based on conditions determining which rows are included in results. WHERE applies conditions to select records but does not create calculated fields with different values based on conditions. WHERE filters existing data rather than deriving new values.
GROUP BY groups records for aggregation but does not provide conditional logic for deriving values. GROUP BY organizes data for summarization rather than implementing conditional transformations within rows.
JOIN combines data from multiple tables based on relationships but does not provide conditional logic for value derivation. JOIN merges data sources rather than applying if-then-else logic to create calculated fields.
CASE expression implementation uses CASE keyword followed by WHEN clauses specifying conditions and THEN clauses specifying return values for those conditions, optionally includes ELSE clause for default values when no conditions match, and ends with END keyword. The entire expression can be used anywhere a column expression is valid including SELECT lists and ORDER BY clauses.
Two CASE syntax forms exist: simple CASE that compares a single expression to multiple values and searched CASE that evaluates independent boolean conditions for each WHEN clause. Searched CASE provides more flexibility for range-based categorization.
Common applications include creating age groups from birth dates using date arithmetic and ranges, assigning risk categories based on multiple factors, converting numeric codes to descriptive labels, and implementing complex business logic that requires multiple conditions. CASE expressions make queries more readable and maintainable compared to alternative approaches.
Question 42
A data analyst needs to combine monthly sales data from separate tables for January, February, and March into a single result set. Which SQL operation stacks results vertically?
A) JOIN
B) UNION
C) CROSS JOIN
D) GROUP BY
Answer: B
Explanation:
Combining data from multiple sources or time periods into unified result sets is common in analytics. SQL provides operations for both horizontal combination through joins and vertical combination through set operations.
UNION combines results from multiple SELECT queries stacking rows vertically into a single result set. Queries being combined must have the same number of columns with compatible data types. UNION by default removes duplicate rows while UNION ALL includes all rows from all queries. For combining monthly sales tables, separate queries select from each month’s table and UNION stacks the results creating a complete quarterly dataset.
JOIN combines tables horizontally adding columns from related tables based on join conditions. JOIN merges data from different tables row by row but does not stack results from separate queries. JOIN is for related data across tables not for combining similar data from different sources.
CROSS JOIN creates cartesian products combining every row from one table with every row from another without join conditions. CROSS JOIN produces all possible combinations and is rarely appropriate for combining monthly data. It multiplies row counts rather than stacking results.
GROUP BY aggregates data grouping records by specified columns. GROUP BY summarizes data within single queries but does not combine results from multiple separate queries. GROUP BY operates on single result sets rather than multiple sources.
UNION implementation requires writing separate SELECT queries for each source with identical column lists, connecting queries with UNION or UNION ALL keywords, and ensuring columns align by position with compatible types. Column names from the first query are used in the result set.
The choice between UNION and UNION ALL depends on whether duplicates should be eliminated. UNION removes duplicate rows comparing entire row values across all columns which requires sorting and can impact performance. UNION ALL simply concatenates all results without duplicate checking making it faster when duplicates are acceptable or known not to exist.
Common patterns include combining historical data split across multiple tables by time period, merging similar data from different regions or systems, and consolidating results from parallel computations. UNION enables building complete datasets from fragmented sources.
Question 43
An analyst needs to grant read-only access to a specific dashboard to team members without allowing them to edit queries or visualizations. Which Databricks SQL feature controls dashboard permissions?
A) Workspace permissions
B) Dashboard access control lists
C) Query parameters
D) SQL warehouse configuration
Answer: B
Explanation:
Controlling access to analytics assets ensures appropriate users can view results while protecting content from unauthorized modifications. Databricks SQL provides granular permission controls for queries, dashboards, and other objects.
Dashboard access control lists enable fine-grained permission management for dashboards. ACLs specify which users and groups can view dashboards (read permission) versus edit dashboard layout and configurations (edit permission) versus manage sharing and permissions (manage permission). Granting read-only access gives team members ability to view dashboards and interact with filters without allowing query or visualization modifications.
Workspace permissions control access to entire workspaces and folders organizing Databricks SQL objects. While workspace permissions provide organizational access control, dashboard ACLs provide object-level permissions enabling sharing specific dashboards with granular access levels.
Query parameters enable users to input values that filter query results but do not control access permissions. Parameters provide interactivity rather than security. Dashboard access requires appropriate permissions regardless of parameter configuration.
SQL warehouse configuration controls compute resources that execute queries but does not govern access to dashboards or analytics content. Warehouse permissions determine who can use compute resources but dashboard permissions control content access.
Dashboard ACL implementation involves opening dashboard settings, navigating to sharing or permissions section, adding users or groups with specified permission levels, and choosing between Can View for read-only access or Can Edit for modification capabilities.
Permission levels include view access allowing dashboard viewing with filter interaction but no editing, edit access enabling layout changes and visualization configuration, and manage access allowing permission changes and dashboard deletion. Appropriate permission assignment follows least privilege principles.
Best practices include creating groups for common access patterns like team members or executives rather than managing individual user permissions, documenting access policies that specify who should have what level of access to different dashboards, and regularly reviewing permissions to ensure they remain appropriate as organizational structures change.
Question 44
A data analyst needs to calculate a running total of sales by date showing cumulative sales through each day. Which SQL technique provides running totals?
A) Simple SUM aggregate
B) Window function with ROWS BETWEEN
C) HAVING clause
D) CROSS JOIN
Answer: B
Explanation:
Running totals accumulate values across ordered rows showing cumulative amounts at each point. Window functions provide mechanisms for computing aggregates that include current and prior rows enabling running total calculations.
Window function with ROWS BETWEEN computes running totals by using SUM with OVER clause specifying ORDER BY for row ordering and frame specification for which rows to include. The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows from the beginning through the current row. As the window slides through ordered data, the running total accumulates incorporating each new row’s value.
Simple SUM aggregate computes totals across entire groups or result sets but does not provide row-by-row accumulation. Standard aggregates collapse multiple rows into single summary values rather than computing per-row cumulative values.
HAVING clause filters aggregated results but does not compute running totals. HAVING works with GROUP BY to filter groups based on aggregate conditions but does not provide window function capabilities for cumulative calculations.
CROSS JOIN creates cartesian products and has no relationship to running total calculations. CROSS JOIN combines tables without considering order or accumulation. It is not useful for time-series calculations.
Running total implementation uses window function syntax with SUM aggregate function, OVER clause to specify the window, ORDER BY to establish row order typically by date, and optional frame specification using ROWS BETWEEN to define which rows are included in each calculation.
The complete expression SUM with column name OVER with ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW computes running totals. Without explicit frame specification, ORDER BY defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which handles ties differently but produces similar results for running totals.
Common applications include cumulative sales showing total revenue through each date, running counts of customer acquisitions showing growth over time, and cumulative averages that incorporate all prior data points. Understanding window functions enables sophisticated analytical calculations that would be complex or impossible with standard aggregation.
Question 45
An analyst needs to compare current month sales to previous month sales for each product. Which SQL technique computes previous period values in the same result set?
A) Self-join with date offset
B) LAG window function
C) HAVING clause
D) UNION with different date filters
Answer: B
Explanation:
Period-over-period comparisons are fundamental to business analytics revealing growth, declines, and trends. Computing prior period values alongside current values enables direct comparison in single result rows.
LAG window function retrieves values from previous rows within ordered result sets enabling period-over-period comparisons. LAG accesses the value from a specified number of rows before the current row based on ORDER BY sequence. For monthly sales comparisons, LAG with offset 1 ordered by date returns previous month sales in the same row as current month enabling direct comparison and variance calculations.
Self-join with date offset joins a table to itself matching each current period row with its corresponding prior period row based on date arithmetic. While self-joins can implement period comparisons, they are more complex than LAG function requiring join conditions and date calculations. LAG provides cleaner syntax for this common pattern.
HAVING clause filters grouped results based on aggregate conditions but does not retrieve prior period values for comparison. HAVING works after aggregation to filter groups but does not provide access to values from different time periods in the same row.
UNION with different date filters combines results from multiple queries but stacks rows vertically rather than placing prior period values in columns alongside current period values. UNION creates longer result sets rather than wider ones with comparative columns.
LAG function implementation uses LAG with the column to retrieve, optional offset defaulting to 1 for immediate previous row, and optional default value for when prior rows do not exist. The function appears within OVER clause specifying ORDER BY to establish row sequence typically by date.
The query structure selects current period columns along with LAG functions that retrieve prior period values as separate columns. Calculated columns subtract prior values from current values showing period-over-period changes. Dividing changes by prior values and multiplying by 100 yields percent changes.
Related window functions include LEAD for accessing future rows useful for forward-looking comparisons, FIRST_VALUE and LAST_VALUE for accessing boundary values within partitions, and NTH_VALUE for accessing specific offset positions. These functions enable comprehensive temporal analysis patterns.