PostgreSQL interview questions

Table sales(id, region, amount) where amount is nullable. For every region that appears in the table return how many sale rows it has and the total of their amounts, treating a missing amount as 0 so that a region whose sales all have NULL amounts reports 0.00 rather than

Table orders(id, customer_id, total) with total NOT NULL. Find the customers who placed at least three orders and whose average order total is strictly greater than 50. Report the customer id, the number of orders and the average rounded to two decimal places.

Table tickets(id, project, status) where status is one of 'open', 'in_progress' or 'closed' and may also be NULL for imported rows. For each project return the total number of tickets, how many are open, and how many are closed. Tickets with a NULL status count towards the

In plain words, what does GROUP BY do to a result set, and what is the difference between COUNT(*) and COUNT(some_column)?

When would you filter in WHERE and when in HAVING? Can a query use both in the same statement?

Table orders(id, customer_id, total) with total NOT NULL. Compute how much each customer spent in total, then return only the customers whose total is strictly greater than the average customer total (the average over customers, not over orders).