Same Question, Four Platforms: SQL vs BigQuery vs Python vs DAX

---

When you learn several data tools at once, it's easy to memorize syntax without understanding why each tool works the way it does. So I ran an experiment: take one dataset (Superstore), ask one business question, and solve it in all four platforms.

The code is on GitHub: CodingFactory

The setup

• Dataset: Superstore Sales (Orders: dates, customers, products, sales, profit)
• Platforms: MS SQL Server, Google BigQuery, Python (Pandas in Colab), Power BI (DAX measures)
• Rule: the same question and the same logic on every platform, with measures used for DAX wherever possible

Challenge 1: Highest spending customer per year

SQL and BigQuery lean on a window function. Python uses idxmax. DAX needs RANKX.

MS SQL

WITH YearlySpend AS (
    SELECT CustomerID, CustomerName, YEAR(OrderDate) AS OrderYear,
           SUM(Sales) AS TotalSpend
    FROM Sales
    GROUP BY CustomerID, CustomerName, YEAR(OrderDate)
),
Ranked AS (
    SELECT *, RANK() OVER (PARTITION BY OrderYear ORDER BY TotalSpend DESC) AS Rnk
    FROM YearlySpend
)
SELECT OrderYear, CustomerID, CustomerName, TotalSpend
FROM Ranked
WHERE Rnk = 1
ORDER BY OrderYear;

BigQuery

WITH yearly_spend AS (
  SELECT CustomerID, CustomerName,
         EXTRACT(YEAR FROM OrderDate) AS order_year,
         SUM(Sales) AS total_spend
  FROM `project.dataset.Sales`
  GROUP BY CustomerID, CustomerName, order_year
),
ranked AS (
  SELECT *, RANK() OVER (PARTITION BY order_year ORDER BY total_spend DESC) AS rnk
  FROM yearly_spend
)
SELECT order_year, CustomerID, CustomerName, total_spend
FROM ranked
WHERE rnk = 1
ORDER BY order_year;

Python

df['OrderYear'] = pd.to_datetime(df['OrderDate']).dt.year
yearly = (df.groupby(['OrderYear', 'CustomerID', 'CustomerName'])['Sales']
            .sum().reset_index(name='TotalSpend'))
top = yearly.loc[yearly.groupby('OrderYear')['TotalSpend'].idxmax()]

DAX

Total Sales = SUM(Sales[Sales])

Customer Rank in Year =
RANKX(
    ALL(Sales[CustomerName]),
    [Total Sales]
)

Put Year and Customer on a table visual and filter Customer Rank in Year = 1.

Challenge 2: Running total

Window frames in SQL, cumsum() in Pandas, and a filter that rebuilds the date range in DAX.

-- SQL Server and BigQuery (same syntax)
SELECT OrderDate, Sales,
       SUM(Sales) OVER (ORDER BY OrderDate
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Sales;

df = df.sort_values('OrderDate')
df['RunningTotal'] = df['Sales'].cumsum()

Running Total =
CALCULATE(
    [Total Sales],
    FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date]))
)

Challenge 3: Month-over-month growth %

SQL, BigQuery and Python all use the same idea: grab the previous row (LAG / shift). DAX uses time-intelligence functions instead, which only work with a proper Date table.

WITH MonthlySales AS (
    SELECT DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS SalesMonth,
           SUM(Sales) AS TotalSales
    FROM Sales
    GROUP BY DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)
)
SELECT SalesMonth, TotalSales,
       ROUND((TotalSales - LAG(TotalSales) OVER (ORDER BY SalesMonth)) * 100.0
             / NULLIF(LAG(TotalSales) OVER (ORDER BY SalesMonth), 0), 2) AS MoM_Growth_Pct
FROM MonthlySales;

m = df.groupby(df['OrderDate'].dt.to_period('M'))['Sales'].sum().reset_index()
m['MoM_Growth_Pct'] = (m['Sales'].pct_change() * 100).round(2)

Prev Month Sales = CALCULATE([Total Sales], PREVIOUSMONTH('Date'[Date]))

MoM Growth % = DIVIDE([Total Sales] - [Prev Month Sales], [Prev Month Sales]) * 100

Challenge 4: 3-month moving average (where DAX bit back)

In SQL, Pandas and BigQuery this is a one-liner:

AVG(TotalSales) OVER (ORDER BY SalesMonth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

monthly['Moving_Avg_3M'] = monthly['Sales'].rolling(window=3, min_periods=1).mean()

In DAX, my first attempt looked right, ran without errors, and returned the wrong numbers. The moving average was identical to the monthly total on every row. Two things caused it:

1. DATESINPERIOD returns a narrow, date-only table. It has none of your other columns.
2. The table visual already filters each row to one month, so unless you clear those filters, the 3-month window never opens.

The fix is to clear the existing date filters, build the 3-month list of months, then average a measure over them:

Moving Avg 3M =
VAR CurrentMonth = MAX('Date'[Date])
VAR Last3Dates   = DATESINPERIOD('Date'[Date], CurrentMonth, -3, MONTH)
VAR Months       = CALCULATETABLE(VALUES('Date'[MonthStart]), ALL('Date'), Last3Dates)
RETURN
    CALCULATE(AVERAGEX(Months, [Total Sales]), ALL('Date'))

Always sanity-check the output against your SQL or Python numbers. DAX won't throw an error for logic mistakes.

The rest of the series

Challenge	SQL / BigQuery	Python	DAX
Year-over-year growth	LAG over years	shift(1)	SAMEPERIODLASTYEAR
New vs returning customers	First-purchase CTE + join	groupby.min + merge	ALL() + VALUES()
Top N products per category	RANK() OVER (PARTITION BY ...)	groupby.rank	RANKX + FILTER
Cumulative distinct customers	Count first purchases, then running total	drop_duplicates + cumsum	DISTINCTCOUNT in CALCULATE
Customer retention	LAG per customer	groupby.shift	DATEADD inside FILTER
RFM segmentation	NTILE(5)	pd.qcut	No NTILE, so RANKX plus math
% of total by category	SUM() OVER ()	/ .sum()	CALCULATE(..., ALL())
Market basket analysis	Self-join	itertools.combinations	Awkward, best done upstream
Profit margin by region	GROUP BY	groupby.agg	3 simple measures
Pareto (80/20)	Running total ordered by value	Sort + cumsum	FILTER comparison
Cohort analysis	Cohort CTE + DATEDIFF	merge + pivot	Calculated columns + a measure

What I learned

1. SQL and Python think in sequences. DAX thinks in filter context. Window functions and shift() move row by row through ordered data. DAX recalculates a measure for whatever filters surround it, which is why ALL(), VALUES() and CALCULATE keep showing up.

2. Columns group, measures aggregate. My cohort table collapsed into a single row because I'd built the grouping fields as measures. A measure has no rows to group by. Anything you want on the rows of a table should be a column, and the numbers you aggregate should be measures.

3. Some problems belong upstream. Market basket analysis is natural in SQL or Python and painful in DAX. Knowing which tool to reach for matters as much as knowing the syntax.

4. Simple questions converge. A profit margin by region looks almost identical on all four platforms. The differences only show up with time comparisons, row-to-row matching and context shifting.

Try it yourself

Pick one dataset and solve the same five questions in every tool you're learning. You'll understand each tool better than you would from isolated tutorials.

Full code and notes: github.com/shijithpulikkal/CodingFactory

If you found this useful, let me know which challenge you'd like me to break down next.