Calculate a revenue and a margin in SQL

Which measurement belongs to which table?

The status belongs to the order, the sold price and quantity to the line, and the cost refers to the product. The query must bring together these levels of detail without adding a repeated measure.

In this educational case, revenue = quantity × unit price and gross margin = quantity × (unit price − cost). A real business must specify taxes, discounts, returns and cost method.

The query explained

Start by defining what a row of the result should represent. This decision determines the necessary columns, groupings, and controls.

The following example isolates the main mechanism. Run it, observe the result, then change one clause at a time to understand its effect.

SELECT p.categorie,
  SUM(l.quantite * l.prix_unitaire) AS chiffre_affaires,
  SUM(l.quantite * (l.prix_unitaire - p.cout)) AS marge
FROM commandes c
JOIN lignes_commandes l ON l.id_commande = c.id_commande
JOIN produits p ON p.id_produit = l.id_produit
WHERE c.statut = 'Paid'
GROUP BY p.categorie
ORDER BY marge DESC;

Read the result like an analyst

The filter excludes unconfirmed orders before calculation. Each paid line contributes according to its quantity, price and cost.

Always present amount and margin rate together: a small category may have an excellent percentage but limited absolute contribution.

• Document the formula and unit.
• Check status included.
• Reconcile the CA with the lines.
• Compare absolute margin and margin rate.

Errors that distort the analysis

A query can be valid without correctly answering the question. The most costly errors are often silent: wrong perimeter, multiplied line or missing value interpreted as zero.

Before sharing the result, compare it to a small hand-calculated sample and keep the scope definition with the query.

• Confusing revenue and collection.
• Forget returns or discounts from the real perimeter.
• Add a repeated order amount after JOIN.
• Divide by zero in the margin rate.

Get into practice

Complete the margin by category exercise, then explain in two sentences why the business ranking may differ from the sales ranking.

The SQL simulator of SQL.tn only runs read queries on dummy data in your browser. You can try multiple writes and compare their results without installing any software.