Search for a command to run...
I'm Mohammed, a passionate software engineer. I've been working on server-side operations for the past six years, but my love for technology started when I was a kid. I love talking about Backend Development, Performance Optimization, Scalability, System Design and Databases. Join me as I explore these topics, sharing insights and experiences from my journey in the tech world.
No comments yet. Be the first to comment.
This chapter dives into the art of managing change in your API. It explores strategies to maintain consistency, ensure backward compatibility, and guide your API’s evolution smoothly while keeping your users happy and your system reliable ✨ Toward Co...
When others rely on your API, availability and reliability become critical. You need to make sure it’s always up, responsive, and fast, even during unexpected usage spikes. A sudden spike in traffic can degrade your service quality or even crash your...
Now that we’ve covered API paradigms, security, and best practices, it’s time to put these concepts into action. This chapter focuses on designing APIs through a hands-on approach using an imaginary example, emphasizing user-focused design and practi...
In the previous chapters, we covered the basics of data transmission and how to choose the right patterns for your API. Now, let’s dive into some practical design tips that will help your developers make the most of your API. Designing for Real-Life ...
API security is vital for web applications because new security risks appear all the time, and a breach can lead to serious data and financial losses. Engineers use methods like SSL, content checks, logs, and protections against attacks like CSRF and...
In many businesses, understanding customer spending patterns can provide valuable insights into consumer behavior. Imagine a company that tracks customer orders and their total spending. Our task is to identify customers who have spent more than the average amount in the last month.
The goal is to write a SQL query to find customers who spent more than the average total spending of all customers in the previous month.
The database contains two tables:
orders:
order_id (INT, primary key)
customer_id (INT, foreign key)
order_date (DATE): The date of the order.
total_amount (DECIMAL): The total amount spent on the order.
customers:
customer_id (INT, primary key)
first_name (VARCHAR)
last_name (VARCHAR)
-- Creating the 'customers' table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(255),
last_name VARCHAR(255)
);
-- Inserting sample data into 'customers'
INSERT INTO customers (customer_id, first_name, last_name) VALUES
(1001, 'John', 'Doe'),
(1002, 'Jane', 'Smith'),
(1003, 'Bob', 'Johnson');
-- Creating the 'orders' table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL
);
-- Inserting sample data into 'orders'
INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(1, 1001, '2024-08-10', 150.00),
(2, 1002, '2024-08-12', 200.00),
(3, 1001, '2024-08-15', 100.00),
(4, 1003, '2024-08-18', 300.00),
(5, 1002, '2024-08-22', 120.00),
(6, 1001, '2024-08-25', 50.00);
The result should list the names of customers who have spent more than the average amount:
first_name | last_name
-----------------------
Jane | Smith
Calculate Total Spending: Determine the total amount spent by each customer in the last month.
Compute Average Spending: Find the average spending across all customers during the same period.
Compare and Retrieve: Identify customers whose spending exceeds the average.
Here’s how you can achieve this with a SQL query:
WITH TotalSpent AS (
SELECT
customer_id,
SUM(total_amount) AS total_spent
FROM
orders
WHERE order_date BETWEEN
DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-01')
AND
LAST_DAY(CURRENT_DATE - INTERVAL 1 MONTH)
GROUP BY
customer_id
),
-- Step 2: Calculate the average total spending of all customers in the last month.
AverageSpending AS (
SELECT
AVG(total_spent) AS avg_spent
FROM
TotalSpent
)
-- Step 3: Retrieve customer names who spent more than the average.
SELECT
c.first_name,
c.last_name
FROM
customers c
JOIN
TotalSpent ts ON c.customer_id = ts.customer_id
CROSS JOIN
AverageSpending a
WHERE
ts.total_spent > a.avg_spent;
TotalSpending CTE: Calculates the total amount spent by each customer within the last month.
AverageSpending CTE: Computes the average spending from the results of TotalSpending.
Final Query: Joins the customers table with TotalSpent to fetch customer details and filters out those who spent more than the calculated average.
This approach efficiently identifies high-spending customers and can be easily adapted for different periods or criteria.