1. This week’s data (Mon–Sun)
WHERE YEARWEEK(col,1)=YEARWEEK(CURDATE(),1)
2. Last week
WHERE YEARWEEK(col,1)=YEARWEEK(CURDATE()-INTERVAL 1 WEEK,1)
3. Last 30 days
WHERE col >= CURDATE()-INTERVAL 30 DAY
4. Inactive >60 days
SELECT * FROM users WHERE last_login < CURDATE()-INTERVAL 60 DAY
5. Sales by month
SELECT DATE_FORMAT(date,'%Y-%m'), SUM(amount) FROM orders GROUP BY DATE_FORMAT(date,'%Y-%m')
6. Customer first/last order
SELECT customer_id, MIN(date), MAX(date) FROM orders GROUP BY customer_id
7. Difference in days
SELECT DATEDIFF(d2, d1) FROM table;
8. Calculate age
SELECT TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) FROM users;
9. Weekend orders
WHERE DAYOFWEEK(col) IN (1,7)
10. Between 9 AM–6 PM
WHERE TIME(col) BETWEEN '09:00:00' AND '18:00:00'
11. Expired subscriptions
WHERE end_date < CURDATE()
12. First-of-month records
13. Working hours
SELECT TIMESTAMPDIFF(HOUR,in_time,out_time) FROM attendance;
14. Null or >1 year old updates
WHERE updated_at IS NULL OR updated_at < NOW() - INTERVAL 1 YEAR
15. Monthly revenue this year
SELECT MONTH(col), SUM(amount) FROM table WHERE YEAR(col)=YEAR(CURDATE()) GROUP BY MONTH(col)
16. Earlier datetime
SELECT LEAST(dt1, dt2) FROM table;
17. Filter quarter (Q1)
WHERE QUARTER(col)=1 AND YEAR(col)=YEAR(CURDATE())
18. Earliest & latest login per user
SELECT user_id, MIN(login), MAX(login) FROM logins GROUP BY user_id;
19. Current fiscal year (Apr–Mar)
WHERE
(
MONTH(col)>=4 AND YEAR(col)=YEAR(CURDATE())
) OR (
MONTH(col)<4 AND YEAR(col)=YEAR(CURDATE())-1
)
