CODEWITHSUNDEEP
sqlgoogle-bigquery
Robin
Robin

Reputation: 1

SQL BigQuery : Calculate Value per time period

I'm new to SQL on BigQuery and I'm blocked on a project I have to compile.

I'm being asked to find the year over year growth of sales in percentage on a database that doesn't even sum the revenues... I know I have to assemble various request but can't figure out how to calculate the growth of sales.

Here is where I am at :

enter image description here Has Anybody an insight on how to do so?

Thanks a lot !

Upvotes: 0

Views: 1531

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173200

Below is for BigQuery Standard SQL 

#standardSQL
SELECT product_line, year, revenue, prev_year_revenue, 
  ROUND(100 * (revenue - prev_year_revenue)/prev_year_revenue) year_over_year_growth_percent
FROM (
  SELECT product_line, year, revenue, 
    LAG(revenue) OVER(PARTITION BY product_line ORDER BY year) prev_year_revenue
  FROM (
    SELECT product_line, year, SUM(revenue) revenue
    FROM `project.dataset.table`
    GROUP BY product_line, year
  )
)
-- ORDER BY product_line, year

Upvotes: 1

Rubén C.
Rubén C.

Reputation: 1098

I tried with your information (plus mine made up data for 2007) and I arrived here:

SELECT
  year,
  sum(revenue) as year_sum
FROM
  YearlyRevenue.SportCompany
GROUP BY
  year
ORDER BY 
  year_sum

Whose result is:

R year year_sum
1 2005 1.159E9
2 2006 1.4953E9
3 2007 1.5708E9

Now the % growth should be added. Have a look here for inspiration.

Let me know if you don't succeed and I will try the hard part, with no guarantees.

Upvotes: 0

Lak
Lak

Reputation: 4166

(1) Starting from what you have, group by product line to get this year and last year's revenue in each row:

#standardsql
with yearly_sales AS (
select year, product_line, sum(revenue) as revenue
from `dataset.sales`
group by product_line, year
),

year_on_year AS (
select array_agg(struct(year, revenue))
            OVER(partition by product_line ORDER BY year
                 RANGE BETWEEN PRECEDING AND CURRENT ROW) AS data
from yearly_sales
)

(2) Compute year-on-year growth from the two values you now have in each row

Upvotes: 1

Related Questions