Context

Financial analysts at Paramount often move from spreadsheet-based cleanup into repeatable SQL workflows for reporting in tools like Paramount+ finance dashboards and ad revenue reporting. Interviewers ask this to see whether you can translate familiar Excel habits into scalable SQL patterns.

Core question

What Excel functions do you use most often for data cleanup and analysis, and how would you perform the same work in SQL? In your answer, explain:

1. Which Excel functions you rely on most for cleanup and analysis
2. The closest SQL equivalents for filtering, lookups, text cleanup, conditional logic, and aggregation
3. When SQL is a better choice than Excel for recurring financial analysis
4. Common mistakes analysts make when moving from spreadsheets to SQL

Scope guidance

Keep the answer practical and role-relevant. The interviewer is not looking for a list of every Excel formula; they want to hear how you think about data wrangling, reproducibility, and scaling analysis in a PostgreSQL environment.

Text cleanup in SQL

Excel functions like TRIM, CLEAN, LEFT, RIGHT, MID, and SUBSTITUTE map to SQL string functions such as TRIM, REPLACE, SUBSTRING, LOWER, and UPPER. These are useful when standardizing campaign names, vendor labels, or transaction descriptions before analysis.

SELECT TRIM(LOWER(REPLACE(vendor_name, '-', ' '))) AS cleaned_vendor_name
FROM finance_transactions;

Conditional logic

Excel IF, IFS, and nested logic are typically replaced with CASE WHEN in SQL. This is the standard way to bucket transactions, flag exceptions, or classify records for reporting.

SELECT transaction_id,
       CASE
         WHEN amount < 0 THEN 'refund'
         WHEN amount = 0 THEN 'zero_value'
         ELSE 'revenue'
       END AS transaction_type
FROM finance_transactions;

Lookups and matching

Excel users often rely on VLOOKUP, XLOOKUP, or INDEX/MATCH. In SQL, the equivalent idea is usually a JOIN, though in an easy conceptual discussion you should emphasize that SQL matches records using keys rather than cell positions.

SELECT t.transaction_id, t.amount, t.cost_center
FROM finance_transactions t;

Aggregation and summarization

Excel SUMIF, COUNTIF, AVERAGEIF, and PivotTables correspond to SQL aggregations using SUM, COUNT, AVG, GROUP BY, and sometimes CASE WHEN. This is central to monthly revenue summaries, expense rollups, and variance analysis.

SELECT cost_center,
       SUM(amount) AS total_amount,
       COUNT(*) AS transaction_count
FROM finance_transactions
GROUP BY cost_center;

NULL handling and data quality

Excel blanks and SQL NULLs are not the same thing, and misunderstanding that causes many reporting errors. In PostgreSQL, COALESCE is commonly used to replace NULL values when appropriate, but analysts should first understand why the value is missing.

SELECT transaction_id,
       COALESCE(cost_center, 'Unassigned') AS cost_center
FROM finance_transactions;