# 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](https://github.com/shijithpulikkal/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**

```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**

```sql
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**

```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**

```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
-- 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;
```

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

```dax
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**.

```sql
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;
```

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

```dax
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:

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

```python
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:

```dax
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**](https://github.com/shijithpulikkal/CodingFactory)

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