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!
How is the week going? Was wondering if I could get the revenue generated globally for just today? Can you please share that number with me?
Best Regards,
Mrs Ahmadu (CGO)
SQL TOPIC OF THE DAY
1. CURRENT_DATE() Function
| SYNTAX SELECT CURRENT_DATE; |
Use Cases and Examples:
– Get Today’s Date:
| SELECT CURRENT_DATE as today_date; |
In this example, the CURRENT_DATE function returns the current date from the system. This can be useful in scenarios where you need to display the current date on a dashboard or a report, providing a timestamp for when the data was generated. This ensures that users are aware of the timeframe of the data they’re viewing.
– Insert Current Date into a Table:
| INSERT INTO orders (order_date, customer_id, total_amount) VALUES (CURRENT_DATE, 1, 100.00); |
Here, the CURRENT_DATE function is used to insert the current date into a new order record. This is particularly useful for tracking when an order was placed without needing to manually enter the date. Automating date entries helps maintain consistency and accuracy in your database.
2. DATE_ADD() Function
| SYNTAX SELECT DATE_ADD(date, INTERVAL value UNIT); |
Use Cases and Examples:
– Calculate Future Dates:
| SELECT DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY) as future_date; |
This example calculates a date 30 days in the future from today’s date. This is useful for scheduling future tasks, setting deadlines, or planning events. For instance, a subscription service might use this function to determine the next billing date for a customer.
– Schedule Reminders:
| SELECT order_id, DATE_ADD(order_date, INTERVAL 7 DAY) as reminder_date FROM orders; |
Here, the function is used to calculate a reminder date one week after the order date. This can be particularly useful in CRM systems for follow-up reminders, ensuring that customers are contacted promptly after their order is placed, thereby improving customer service and engagement.
SQL PRACTICE
Now, let’s write a script to answer the question of the day.
Mrs Ahmadu would like you to provide the revenue generated globally for just today
| SELECT SUM(price_paid) as global_revenue FROM orders WHERE order_date = CURRENT_DATE; GROUP BY 1 |
FEEDBACK FROM THE CGO
Thanks for sharing that so quickly. What about the revenue from yesterday?
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!
