Internal reporting platform

Context

Six people emailed monthly reports. Each had their own format, each calculated things differently, and every meeting started with half an hour of arguing about which number was right.

The approach

Put the data in one place and write each report once. The interface is plain: pick a period, pick a report, view it in the browser or download it.

SELECT r.region, SUM(o.total) AS revenue
  FROM orders o
  JOIN regions r ON r.id = o.region_id
 WHERE o.placed_at BETWEEN :from AND :to
 GROUP BY r.region
 ORDER BY revenue DESC

The heavy queries run overnight and write into a summary table. During the day the reports read from the summary and open instantly.

What I learned

The hard part was not technical. It was getting six people to agree on a single definition of the word “sale”.