Excel, SAP, Oracle & automation tips — plus wacky facts
Final project: recency, frequency and monetary scores with NTILE, customer segments, outstanding receivables per customer and average collection days by payment mode.
Read article →Read EXPLAIN QUERY PLAN, see SCAN turn into SEARCH with an index, build covering indexes, and learn why functions on columns stop indexes working.
Read article →Turn rows into columns with conditional aggregation: sales by status per region, orders per quarter, payment-mode mix and actual vs target with % achieved.
Read article →Walk a manager hierarchy, build reporting paths, total a team's salary cost and generate a calendar to find days with no orders, using WITH…
Read article →Combine and compare result sets: one list from two tables, customers who bought both products, customers who never bought one, and activity timelines with…
Read article →Find unpaid orders with LEFT JOIN ... IS NULL and NOT EXISTS, list employees with their managers via a self-join, and fill zero months…
Read article →Compare each row with the previous or next one: month-on-month growth %, days between a customer's orders and the next order date with LAG…
Read article →Running totals, year-to-date by region, 3-month moving averages and share of total with SUM() OVER and window frames (ROWS BETWEEN).
Read article →Rank customers, find the top customer in each region and each customer's latest order with ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY.
Read article →Write readable SQL with CTEs: name a subquery once, chain several steps, compare each customer with the average and compute category shares.
Read article →StatisticsLevel: ExpertAvailable in: Excel 2010+F.INV returns the F value for a left-tail probability.Syntax=F.INV(probability, deg_freedom1.
Read article →LOGEST fits y = b·mˣ and returns m (the growth factor) and b. m of 1.05 means 5% growth per period.
Read article →
