QUADRANT RESOURCE LLC Senior Data Analyst Interview Questions and Answers

YTD in DAX calculates cumulative totals from the beginning of the year to the current date.

• Use the DAX function TOTALYTD to calculate YTD values.

• Syntax: TOTALYTD(<expression>, <dates>, [<filter>], [<year_end_date>])

• Example: YTD Sales = TOTALYTD(SUM(Sales[Amount]), Dates[Date])

• Ensure your date table is marked as a date table in the model.

• You can apply additional filters to the YTD calculation.

DAX uses time intelligence functions to calculate previous month's sales effectively.

• Use the 'PREVIOUSMONTH' function to reference the previous month.

• Example: 'Sales Last Month = CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH(Sales[Date]))'.

• Ensure your date column is in a proper date format for accurate calculations.

• Combine with 'FILTER' for more complex scenarios, like specific product sales.

Year-over-Year (YoY) in DAX measures the change in a metric compared to the same period in the previous year.

• Use the DAX function 'SAMEPERIODLASTYEAR' to compare current year data with the previous year.

• Example: YoY Sales = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Date[Date]))

• Ensure your date table is properly marked as a date table for accurate calculations.

• YoY can also be calculated using 'DATEADD' for more f...read more

Calculate a moving rolling average in DAX to analyze trends over time.

• Use the DAX function AVERAGEX to compute the average over a specified period.

• Example: Moving Average = AVERAGEX(DATEADD('Table'[Date], -n, DAY), 'Table'[Value]) where n is the number of days.

• Combine with CALCULATE to filter the data context for accurate results.

• Consider using the DATESINPERIOD function to define the rolling window.

Cumulative or running total in DAX calculates the sum of values up to a specific point in time.

• Cumulative total adds values sequentially over a defined period.

• Example: For sales data, a cumulative total for January would sum all sales from the start of the month to each day.

• DAX function: CALCULATE(SUM(Sales[Amount]), FILTER(ALLSELECTED(Sales[Date]), Sales[Date] <= MAX(Sales[Date])))

• Useful for trend analysis, forecas...read more

Identify duplicate records in SQL using GROUP BY and HAVING clauses.

• Use GROUP BY to group records by the column(s) you want to check for duplicates.

• Apply the HAVING clause to filter groups that have a count greater than 1.

• Example: SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;

• You can also use ROW_NUMBER() for more complex scenarios to identify duplicates.

Window functions perform calculations across a set of table rows related to the current row, without collapsing the result set.

• Window functions allow you to perform calculations like running totals, averages, and rankings over a specified range of rows.

• They are defined using the OVER() clause, which specifies the partitioning and ordering of the data.

• Example: SELECT employee_id, salary, SUM(salary) OVER (PARTITION BY d...read more

To find the 2nd highest salary in SQL, we can use various methods like subqueries or the DISTINCT keyword.

• Using Subquery: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

• Using DISTINCT: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

• Using ROW_NUMBER(): SELECT salary FROM (SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees) ...read more