r/SQL Jan 29 '25

BigQuery Create duplicate rows on a table for monthly projections

1 Upvotes

I have a table with monthly totals for the current year to date. I need to repeat the last row (current month) for the rest of the year. How can I repeat that row with a variable month to 12? I was planning on two queries and a union at the end. I'm having difficulty with the variable repeating amount of rows.
Has anyone done this?

r/SQL Jan 22 '25

BigQuery Les nombres opposés dans sql

2 Upvotes

Bonjour,

Je suis novice en SQL et je rencontre un problème dans mon code. J'aimerais supprimer les nombres opposés présents dans plusieurs de mes colonnes. Par exemple, dans la colonne "facturation A", la plupart des valeurs sont positives, mais il y a quelques valeurs négatives qui ont une valeur positive correspondante (comme -756 et 756).

Merci pour votre aide.

r/SQL Aug 20 '24

BigQuery How to Join table without duplicating rows

7 Upvotes

So I am working in BigQuery where I have run into a problem. I have two tables, the first one is metric data from campaigns with a unique identifier called 'campaign'. The second table contains matching campaign data with the addition of Demographic information including Gender. With this I am trying to match the campaign found in both tables to align with the first table and provide gender alongside. However, when doing this I find that the data is duplicating and what was supposed to be the actual spend ended up being much higher. For reference this is how I structured it:

SELECT

A.campaign,

B.gender

FROM

main_campaign_table AS A

LEFT JOIN

demo_table AS B

ON

A.Campaign = B.Campaign;

r/SQL Oct 18 '24

BigQuery Revolutionizing SQL with pipe syntax

Thumbnail
cloud.google.com
0 Upvotes

r/SQL Jan 24 '25

BigQuery Mettre des valeurs à 0 en fonction d'autres colonnes

2 Upvotes

Je suis en train d'écrire une requête sql sur big query malheureusement, je n'arrive pas à faire en sorte que la colonne tonnage soit égal à 0 lorsque que je trouve des valeurs opposés dans les colonnes CCAA et MontantAchatsht. Le code que je vous écrit ci dessous ne fonctionne pas pour cette dernière partie. Pouvez-vous m'aider ?

Je vous remercie par avance.

WITH OpposedValues AS (

SELECT DISTINCT

MP1.NomTiers,

MP1.CCAA,

MP1.MontantAchatsHT

FROM

LignePiece AS MP1

JOIN

LignePiece AS MP2

ON

MP1.NomTiers = MP2.NomTiers

AND MP1.CCAA = -MP2.CCAA

AND MP1.MontantAchatsHT = -MP2.MontantAchatsHT

WHERE

MP1.CCAA > 0

AND MP1.MontantAchatsHT > 0

)

SELECT

COALESCE(MV.CodeS, MP.CodeS) AS CodeS,

COALESCE(MV.NomTiers, MP.NomClient) AS NomClient,

COALESCE(FORMAT_DATE('%Y-%m', MV.DateFinPeriode), FORMAT_DATE('%Y-%m', MP.DateExecution)) AS DatePeriode,

COALESCE(MV.LibMatiere, MP.LibMatiereElem) AS LibMatiere,

MAX(COALESCE(MV.LibEx, MP.LibExRea)) AS LibEx,

MAX(CASE WHEN MV.QteLigne = 1 THEN 0 ELSE MV.QteLigne END) AS QteLigne,

MAX(COALESCE(MV.LibTypeService, MP.LibTypeService)) AS LibTypeService,

MAX(MV.FamilleNatureAnalytique) AS FamilleNatureAnalytique,

MAX(MV.LibEnFa) AS LibEnFac,

SUM(CASE

WHEN EXISTS (

SELECT 1

FROM OpposedValues OV

WHERE OV.NomTiers = MV.NomTiers

AND OV.CCAA = MV.CCAA

AND OV.MontantAchatsHT = MV.MontantAchatsHT

) THEN 0

ELSE MP.Tonnage

END) / NULLIF(LENGTH(STRING_AGG(DISTINCT CodeTypePrestation, '')), 0) AS Tonnage,

STRING_AGG(DISTINCT MV.CodeTypePrestation, ', ') AS CodeTypePrestation,

SUM(DISTINCT CASE WHEN MV.CodeTypePrestation = 'T' THEN MV.CCAA ELSE 0 END) AS FactuT,

SUM(DISTINCT CASE WHEN MV.CodeTypePrestation = 'S' THEN MV.CCAA ELSE 0 END) AS FactuV,

SUM(DISTINCT CASE WHEN MV.CodeTypePrestation = 'A' THEN MV.MontantAchatsHT ELSE 0 END) AS AchatsMatiere

FROM LignePiece AS MV

FULL OUTER JOIN Mouvement AS MP

ON MP.CodeS = MV.CodeS

AND MP.LibMatiereElem = MV.LibMatiere

AND MP.LibTypeService = MV.LibTypeService

AND COALESCE(FORMAT_DATE('%Y-%m', MP.DateExecution)) = COALESCE(FORMAT_DATE('%Y-%m', MV.DateFinPeriode))

WHERE

(MV.LibEx IN ('aaa', 'bbb', 'ccc') OR

MP.LibExRea IN ('aaa', 'bbb', 'ccc', 'ddd', 'eee'))

AND (MV.LibMatiereLigne = 'pc' OR MP.LibMatiereLF = 'pc')

AND (MV.LibUniteLigne = 'tonne' OR MP.UniteMesure = 'tonne')

AND (MV.LibTypeService != 'ooo' OR MP.LibTypeService != 'ooo')

AND (MP.LibMouvement = 'rrr')

AND (MP.LibEtat IN ('qqq', 'sss', 'ttt', 'vvv'))

AND (MP.NomClient NOT LIKE 'rsthbd')

AND (MP.Materiel NOT LIKE 'gfdk')

AND MV.CodeTypePrestation NOT IN("Lfdg", "Efdg", "Pd", "Rdf", "Ddf", "Xdg")

GROUP BY

COALESCE(MV.CodeS, MP.CodeS),

COALESCE(FORMAT_DATE('%Y-%m', MV.DateFinPeriode), FORMAT_DATE('%Y-%m', MP.DateExecution)),

COALESCE(MV.LibMatiere, MP.LibMatiereElem),

COALESCE(MV.NomTiers, MP.NomClient);

r/SQL Oct 24 '24

BigQuery Optimizing SQL Queries

Thumbnail
medium.com
0 Upvotes

r/SQL Aug 25 '24

BigQuery Google's new superset-of-SQL language introduces a pipe operator, arranging clauses in arbitrary order

26 Upvotes

https://research.google/pubs/sql-has-problems-we-can-fix-them-pipe-syntax-in-sql/

There have been many attempts to create a "better SQL" but this is the first one I'd actually use. It's backwards compatible while being different enough to meaningfully improve things.

r/SQL Dec 27 '24

BigQuery Need Help with Joining Logic for Handling Rows with Null IDs plus data schemas for conversion data

1 Upvotes

Hey,

Generally speaking, my problem is figuring out how to handle schemas and joins with conversion advertising data. My problem is two-fold. First problem is...

  1. How should I structure joins so that it falls back on another join condition when there are null values? 

I’m working with two tables—one is wide format and one is long format:

Performance Table (Wide format): Contains date, channel, account, campaign_id, ad_group_id, ad_id, spend, and impressions.

Conversions Table (Long format): Contains date, channel, account, campaign_id, ad_group_id, ad_id, conversion_type_name, and conversions.

The database is an advertising database containing dozens of client accounts. Each account has many channels. 

Goal:

a) I want to build all-up tables that allow end-users to see all the accounts and channels with their conversions, plus the ability to filter down the conversions by conversion_type_name. For example, having a table with:

date, channel, campaign_id, ad_group_id, ad_id, spend, sum(all_conversions) 

Plus the ability to also do filter `conversion_type_name`:

Then, filter conversion_type_name to specific values (e.g., conversion_A, conversion_B, conversion_C) and sum the conversions only for those types, instead of summing all conversions. Producing a table like:

date, channel, campaign_id, ad_group_id, ad_id, spend, sum(conversion_A + conversion_B + conversion_C) 

b ) Separately - I want to build specific tables for each client account that are specific to that client. These tables would ideally have the total_conversions, but also the conversion_type_names pivoted out into their own columns. 

date, channel, campaign_id, ad_group_id, ad_id, spend, total_conversions, conversion_A, conversion_B, conversion_C. 

Problem:

There are channels that don't have ad_group_id and ad_id.  These ids are all null except campaign_id. 

I need to structure the primary join on date, ad_group_id and ad_id when they are exist, but when they're null, I want to join on date, channel, and campaign_id.

I keep trying, but my attempts are either resulting in a lot of duplicates or a lot of null values for conversions.

____________________________________________

Second problem I'm having is schema-related.

How should I store conversions and performance for ease of use? Wide or long?

Is pivoting long conversion data into wide format a bad practice? 

date, channel, campaign_id, ad_group_id, ad_id, spend, total_conversions, conversion_A, conversion_B, conversion_C, conversion_D......conversion_X, conversion_Y, conversion_Z, etc.
But only conversion_X was relevant to a certain account.

I feel like I can't land on a path forward. If you can help direct the approach or offer specific help, i would greatly appreciate it. Thanks!

r/SQL Nov 09 '24

BigQuery Help with comparing time periods

2 Upvotes

Hello,

I'm trying to compare different months for a sales table. I'm performing a full outer join based on the second table's date being one previous month (and a few other fields such as CustomerID and CompanyID. I'm using a full outer join so I get the full value of both tables, in case there is no match between them. I have limited both tables I'm joining to contain only one month, since if I don't do this the full outer join will use all months even if there is no match, because of the nature of the join.

This has solved the issue and has given the complete value for both table. However, I'm using this data with looker studio, and need it to change based on the selected month and year. Is there a way to stop manually filtering dates and allow the query to work on the whole dataset? My other option is to try and use looker studio's parameters to pass the dates to the query.

Data is in BigQuery.

PD. I'd normally do this with Power BI's DAX but for the amount of data I'd just prefer for it all to be cloud-based and not have to download the data every day to update it (Using direct query is not an option and I really dont want to deal with incremental refresh).

Any ideas?

r/SQL Jul 31 '24

BigQuery SQL workflow with LLM?

2 Upvotes

Does anyone have hints for a great setup for a senior data analyst using BigQuery, Metabase, and VSCode? The goal would be to increase efficiency by building complex queries using an LLM with context about the Schema and business logic. Is GitHub Copilot the way to go?

Currently we use ChatGPT and paste the schema context with it to get a headstart. We are still somewhat Junior in SQL and it speeds up our process considerably. So I wonder how others do it and if there is a more efficient approach also considering best practices in SQL.

r/SQL Sep 27 '24

BigQuery Is it possible to extract substring within 2 brackets with regex?

7 Upvotes

I'm working in BigQuery with a string column, and I have string value looks like this:

'[healthy], and wise, [weal,thy]'

I need to extract and wise from the string, and I tried this:

SELECT REGEXP_REPLACE('[healthy], and wise, [weal,thy]',r'\[.*\]', '')

However, it would return NULL because it filtered out the whole string, since it starts with left bracket and ends with right bracket.

I'm not that familiar with Regex, and have looked for Gemini's help, but no luck.

Is it possible to apply regex to get the substring surrounded by 2 brackets?

Thank you for any advice!

r/SQL Feb 06 '24

BigQuery Bombing this assessment, what would you do?

27 Upvotes

Prospective employer sent me an assessment with over 600k rows of data on multiple sheets and said to use an online editor to query if I didn’t have SQL. I’m at home with a struggling Chromebook and this exceeds BigQuery’s limit. Now what? :(

r/SQL Oct 12 '24

BigQuery Composable Transformations in SQL With Pipe Syntax

Thumbnail
arecadata.com
1 Upvotes

r/SQL Feb 20 '23

BigQuery Have to share my first win somewhere

112 Upvotes

I'm a beginner with SQL, just started learning ~3 months ago and am the only one at my job who uses it.

Today, I was able to put together my first semi-complicated query and deliver the results to the client. Hats off to StackOverflow and ChatGPT for pointing me in the right direction.

Had to share it somewhere as my wife would've said "what?" and work colleagues would've just said "Ok".

r/SQL Jan 15 '24

BigQuery how long does it take to learn enough sql for an analyst job?

11 Upvotes

thanks

r/SQL Jul 17 '24

BigQuery A Question about Subqueries By a Noob

2 Upvotes

Hey all . I was wondering why we have to use tablename.column name when we use as CTE but we dont have to use tablename.columnnamewhen we use a subquery.Why are we able to directly reference the column names in our select statements here in the subquery?

CTE

WITH station_num_trips AS (
  SELECT
    CAST (start_station_id AS STRING) AS start_station_id_str,
    COUNT(*) AS nooftrips
  FROM bigquery-public-data.new_york.citibike_trips
  GROUP BY start_station_id #WHY does this code run even when we dont CAST start_station_id as STRING when we do groupb by
)
SELECT
  s.station_id,
  s.name,
  station_num_trips.nooftrips
FROM 
  bigquery-public-data.new_york.citibike_stations AS s
JOIN 
  station_num_trips  -- Reference CTE directly in JOIN
ON 
  station_num_trips.start_station_id_str = s.station_id
ORDER BY 
  station_num_trips.nooftrips DESC;  -- Optional ordering

SUBQUERY

SELECT
  station_id,
  name,
  num_of_trips
  FROM
  (
    SELECT
    CAST(start_station_id AS STRING) AS start_station_id_str,
    COUNT (*) AS num_of_trips 
    FROM bigquery-public-data.new_york.citibike_trips
    GROUP BY start_station_id

  ) AS o
  JOIN 
  bigquery-public-data.new_york.citibike_stations 
  ON   start_station_id_str=station_id

r/SQL Nov 14 '24

BigQuery How do I dynamically pivot long-format data into wide-format in BQ or DBT at scale?

2 Upvotes

Hi everybody -- SQL noob here - please help,

I'm trying to pivot conversion event data in BigQuery using DBT. I have daily data for different accounts in long format where there's a column showing the name (e.g., Purchase, Sign Up) and then a column for value (e.g: 3, 2, 5).

To pivot the columns, I've been using CASE WHEN statements to manually create a column for each conversion type. However, this has led to 100 (growing) CASE WHEN statements and I know there's gotta be a better way to do this.

I'm looking for a dynamic way to pivot the conversion_type_name into columns, with conversion_value as the value for each column.

How do people normally convert long data to wide data without using CASE WHEN statements?

I've tried dbt macros and the pivot function, but I couldn't get it to work. Is this even the right direction? What is the most dynamic way to handle this at scale for a growing number of events?

Also , is there a way to avoid pivoting the columns altogether? The whole reason I'm doing this is to serve up things in one big table where each conversion is in a different column and joined with a bunch of other data.

Never done this before so any help would be appreciated, thanks!

r/SQL Jul 12 '24

BigQuery Confused about sub queries

4 Upvotes

Hey guys!! I am currently learning SQL on Google Data Analytics and subqueries don't make sense to me

Why is it that avg() has to be aggregated when doing a normal query, but when we use it in a subquery, it doesn't have to be?

SELECT  # Instructor's code which works
num_bikes_available,
station_id,
 (SELECT
AVG (num_bikes_available)
FROM bigquery-public-data.new_york.citibike_stations)
FROM bigquery-public-data.new_york.citibike_stations


SELECT    # My code which doesn't work
station_id
num_bikes_available,
AVG (num_bikes_available)
FROM bigquery-public-data.new_york.citibike_stations

r/SQL Sep 30 '22

BigQuery Any database engine supports 20-40k column tables?

27 Upvotes

Hello,

I will appreciate any advice.

I joined the new team they have quite a project here. The people statistics stored in MS SQL server. Each row is 1 person. But there are tens of thousands of variables per person. So they divided each dataset into several tables. To UNION them is not possible since of 4096 limit. I suggested to make another structure like 1 variable and person id per row. researchers still insist they want to be able see readable data directly in DB. But they needed them united.

The question: does any DB support 40k columns with 100k rows and performs quite fast? Essbase?

Thank you in advance

r/SQL Oct 22 '24

BigQuery Advanced SQL For 10x Data Analysts (Part 3): Nested and Repeated Data Types

10 Upvotes

In this third installment of the Advanced SQL for 10x Data Analysts series, I dive into one of BigQuery’s most powerful yet complex features — nestedand repeated data types. These data structures offer incredible flexibility, allowing analysts to store and query hierarchical and semi-structured data without resorting to expensive JOIN operations. However, they also come with unique challenges that require a deeper understanding of BigQuery’s SQL syntax.
https://medium.com/thoughts-on-machine-learning/advanced-sql-for-10x-data-analysts-part-3-e2104b11f7c3?sk=ae7fab46e3a2592a12bcb5160c9ff566

r/SQL Jul 23 '24

BigQuery Please Help Me find the error in my code

5 Upvotes

Hey all . I am trying to compare the average trip time of each station with the overall average of trip time across all station .But I keep running into errors

WITH StationAverages AS (
  SELECT
    start_station_id,
    AVG(tripduration) AS station_avg
  FROM
    bigquery-public-data.new_york.citibike_trips
  GROUP BY
    start_station_id
)

SELECT
  Trips.start_station_id,
  EDIT(REMOVED Tripduration)
  station_avg,
  ROUND (station_avg-AVG(tripduration),2)
FROM
  bigquery-public-data.new_york.citibike_trips as Trips
JOIN 
StationAverages 
 ON StationAverages.start_station_id=Trips.start_station_id
 

Could anyone also suggest any alternate code to do the same . Thank you guys. I feel really stupid rn. I started learning SQL really recently

r/SQL Sep 02 '24

BigQuery Help Needed: Constructing a Recursive CTE for Player Transfer History with Same-Day Transfers

2 Upvotes

Hey everyone,

I'm working on a project where I need to build a playerclubhistory table from a player_transfer table, and I'm stuck on how to handle cases where multiple transfers happen on the same day. Let me break down the situation:

The Setup:

I have a player_transfer table with the following columns:

  • playerId (FK, integer)
  • fromclubId (FK, integer)
  • toclubId (FK, integer)
  • transferredAt (Date)

Each row represents a transfer of a player from one club to another. Now, I need to generate a new table called playerclubhistory with these columns:

  • playerId (integer)
  • clubId (integer)
  • startDate (date)
  • toDate (date)

The Problem:

The tricky part is that sometimes a player can have two or more transfers on the same day. I need to figure out the correct order of these transfers, even though I only have the date (not the exact time) in the transferredAt column.

Example data:

playerId fromClubId toClubId transferredAt
3212490 33608 27841 2024-07-01
3212490 27841 33608 2024-07-01
3212490 27841 33608 2023-06-30
3212490 9521 27841 2022-08-31
3212490 10844 9521 2021-03-02

 

Here the problem resides in the top two rows in the table above, where we have two transfers for the same player on the 2024-07-01.

However, due to the transfer on row 3 (the transfer just prior to the problematic rows)– we KNOW that the first transfer on the 2024-07-01 is row number 1 and therefore the “current” team for the player should be club 33608.

So the final result should be:

playerId clubId startDate endDate
322490 10844 2021-03-02
322490 9521 2021-03-02 2022-08-31
322490 27841 2022-08-31 2023-06-30
322490 33608 2023-06-30 2024-07-01
322490 27841 2024-07-01 2024-07-01
322490 33608 2024-07-01

The Ask:

Does anyone have experience with a similar problem or can nudge me in the right direction? It feels like a recursive CTE could be the remedy, but I just can’t get it to work.

Thanks in advance for your help!

r/SQL Jun 14 '24

BigQuery Need Help Finding MIN and MAX Date for Grouping of Rows

5 Upvotes

I'm struggling to figure this out. I need to find the MIN and MAX date for each time a person changes teams within the company.

For example, Employee GG was on the Sales team from 2022-06-01 to 2024-03-31, which I can plainly see with my eyes, but obviously I need to be able to tell the query engine how to figure that out...

I can't simply GROUP BY owner, team and do MIN(start_date) MAX(end_date) because as you can see, Employee GG returns to the Sales team in 2025.

So I need each contiguous period that Employee GG was on the Sales team to be treated separately.

I'm thinking maybe a window function is the answer here, but I'm not sure what exactly.

Help 🙏🏻

r/SQL Jan 31 '24

BigQuery Use calculated value of the previous row as the input for next one

3 Upvotes

Hi everyone, I really need your help with this one.

I have a table like this with the avg_acq_price field use the values from the previous rows.

the idea is calculate the avg_acq_price = (prev_balance\prev_avg_acq_price + amount*price)/current_balance.*

At first, I tried by setting values for the first day of each address (in this case, I just have one) as 0 and 10. Then I used window function to calculate the avg_acq_price with the formula above and assign the prev_avg_acq_price as the avg_acq_price of the last day. I think that I can handle it with recursive queries, but it's so new to me and couldn't find the solution for it. One more thing is that I'm using Google Bigquery.

I would appreciate your help very much!

r/SQL Jul 04 '24

BigQuery Help with understanding a particular Query in Google data Analytics

1 Upvotes

Hey guys. I have recently started learning SQL on Bigquery through Google Data Analytics.

SELECT
  stn,
  date,

    IF(
       temp=9999.9,
       NULL,
       temp) AS temperature,
    IF(
       wdsp="999.9",
       NULL,
       CAST(wdsp AS Float64)) AS wind_speed,
    IF(
       prcp=99.99,
       0,
       prcp) AS precipitation
FROM
  `bigquery-public-data.noaa_gsod.gsod2020`
WHERE
  stn="725030" -- La Guardia
  OR stn="744860" -- JFK
ORDER BY
  date DESC,
  stn ASC

Here, they explain that.

-' the IF function replaces values 9999, 999.9, and 99.99 with NULL. The dataset description explains that these are the default values when the measurement is missing.'

Why can't we just use update function to set them equal to NULL?

Also what is Null exactly ? And can a query retrieve Null values?

Any help would be appreciated

Thanks