Working with dates is a fundamental aspect of database management and data analysis. SQL provides a rich set of date functions to handle everything from simple date arithmetic to complex date manipulation.
There are many ways to build SQL competency so we have decided to take a different approach.
For the next few days, you will be serving as the newly hired Data Analyst for Basira and Co, An Afro fusion restaurant with a network of chains across Africa and you’ll be reporting to the Chief Growth Officer (CGO) Mrs Ahmadu.
Everyday, you’ll get analytical requests/tasks from her related to the SQL topic of the day. The goal for this is to make each lesson as practical and as relatable as possible.
HOW TO PRACTICE: Here is a link to our practice guide. We have already prepared the table schema in advance so all you need to do is plug that into DBFiddle.com and you are all set! The LinkedIn forum is a great way to share any comments or questions about the practice guide.
YOU’VE GOT MAIL!
Hello! It seems we have some restaurants that have been opened without any sales for the past week. Can you confirm this?
Best Regards,
Mrs Ahmadu (CGO)
SQL TOPIC OF THE DAY
1. DATE_SUB() Function
| SYNTAX SELECT DATE_SUB(date, INTERVAL value unit); |
Use Cases and Examples:
– Billing Periods:
| SELECT customer_id, DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH) as billing_start_date FROM customers; |
By subtracting one month from the current date, this query helps determine the start of the current billing period. This is particularly useful in financial applications where accurate billing cycles need to be maintained to ensure proper invoicing and payment tracking.
– Analyze Historical Data:
| SELECT * FROM sales WHERE sale_date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY); |
This query fetches sales records from the past week, which is useful for generating weekly sales reports. Businesses can use this data to analyze short-term sales performance, identify trends, and make adjustments to their strategies accordingly.
2. DATEDIFF() Function
| SYNTAX SELECT DATEDIFF(date1, date2); |
Use Cases and Examples:
– Calculate Days Between Dates:
| SELECT order_id, DATEDIFF(delivery_date, order_date) as days_to_deliver FROM orders; |
This query calculates the number of days it takes for an order to be delivered. This information is critical for logistics and supply chain management, helping businesses to monitor delivery performance and improve their shipping processes.
– Project Timeline:
| SELECT project_id, DATEDIFF(end_date, start_date) as project_duration FROM projects WHERE status = ‘ongoing’; |
This example measures the duration of ongoing projects. Knowing the time span of projects helps project managers to track progress, allocate resources efficiently, and identify projects that are at risk of running over their deadlines.
SQL PRACTICE
Now, let’s write a script to answer the question of the day.
Mrs Ahmadu would like you to provide the restaurants that have been opened without any sales for the past week
| SELECT order_id, DATEDIFF(CURRENT_DATE(), order_date) as days_since_order FROM orders WHERE DATEDIFF(CURRENT_DATE(), order_date) > 7 |
FEEDBACK FROM THE CGO
This is very concerning indeed. Just out of curiosity can you provide all orders in the past month for the restaurants in question?
Warm regards,
Mrs Ahmadu (CGO)
With what we learned today, think you can solve this part on your own?
Did you learn anything new? Share your new insights with us on Facebook, LinkedIn or Instagram and don’t forget to tag us!
