A bit unfamiliar with this but wondering how I can use a similar function on SQL as this Google Sheets function to pull "average" monthly revenue based on the start_date a product started selling.
=iferror(H200DATEDIF(VLOOKUP($A172,'DATA'!$A$2:$H$1000,13, false),TODAY(),"M"),0)
on SQL, if I were to want to pull the average monthly revenue from TODAY and by the number of months the product has been released, how would that look instead?
So far, I have:
SELECT
`product`.`id` AS `id`,
DATE_FORMAT(`product`.start_selling_date, "%m/%d/%Y") AS `release_day`,
`product`.`revenue` as `rev`,
FROM `product`
The goal would be to take the rev part which gives me total revenue and divide it by the number of months it's been since the release_day.
Example: Product A launched on 2022-01-21 and has $10,000 in revenue. The average monthly would be around $1,428.57 (7 months). If it's possible to have SQL calculate that automatically when pulling the rest of the information (product ID, release_day, revenue, average monthly revenue)
Is it maybe something like DATEDIFF?
Thank you!
Yes, DATEDIFF(). Assuming the date field is an actual date or datetime field, something like...
SELECT
product.revenue / ( DATEDIFF( CURDATE(), product.start_selling_date ) / 30 ) AS average_monthly_revenue
I am using 30 day months here, but you could get more accurate if needed.