PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres Query to join multiple tables properly

I want to join two tables to get the output as shown in Table4. Below are the details:

Table1: SalesData

Date PlatformId UnitsSold Revenue ChannelId
2024-07-01 ABCD1 12 1200 1
2024-07-02 ABCD1 11 1000 1
2024-07-01 ABCD2 4 1200 2
2024-07-03 ABCD3 42 9000 3
2024-07-03 ABCD3 10 1000 2

Table2: AdsData

Date PlatformCode AdSpend
2024-07-01 1A 1000
2024-07-02 1A 1500
2024-07-01 2B 4000
2024-07-03 3C 4200

Table3: PlatformMapping

PlatformId Platformcode
ABCD1 1A
ABCD2 2B
ABCD3 3C

Desired Output (Table4)

Date PlatformId UnitsSold Revenue ChannelId AdSpend
2024-07-01 ABCD1 12 1200 1 1000
2024-07-02 ABCD1 11 1000 1 1500
2024-07-01 ABCD2 4 1200 2 4000
2024-07-03 ABCD3 32 9000 3 3200
2024-07-03 ABCD3 10 1000 2 1000

Here, the AdSpend is joined based on the PlatformId and Date with PlatformCode from Table3 and Table2.

I tried writing the following queries using CTEs but they didn’t work as expected:

WITH AdSpendMapped AS (
    SELECT
        t1.date,
        t1.platformid,
        t1.UnitsSold,
        t2.AdSpend
    FROM SalesData t1
    JOIN PlatformMapping t3 ON t1.platformid = t3.PlatformId
    JOIN AdsData t2 ON t2.PlatformCode = t3.Platformcode AND t2.date = t1.date
),
TotalUnits AS (
    SELECT
        date,
        platformid,
        SUM(UnitsSold) AS TotalUnits
    FROM SalesData
    GROUP BY date, platformid
)
SELECT
    a.date,
    a.platformid,
    a.UnitsSold,
    a.ChannelId,
    COALESCE((a.AdSpend * a.totalquantitysold / NULLIF(t.TotalUnits, 0)), 0) AS AdSpend
FROM AdSpendMapped a
JOIN TotalUnits t
    ON a.date = t.date AND a.platformid = t.platformid;
Tags:

2

Answers


  1. 0

    Your query cannot work, because you access AdSpendMapped.ChannelId in your main query, but your AdSpendMapped CTE doesn’t have a ChannelId in its select list.

    You want to show all salesdata rows. Along with them you want their adsdata.adspend divided by the ratio of unitssold within a group of platformid and date.

    For the ratio calculation you can use the window function SUM OVER. In PostgreSQL the division operator / gives you an integer result when working with integers (like we did in primary school). So, we must change our integer column to a decimal first in order to get proper results.

    SELECT
  sd.date,
  sd.platformid,
  sd.unitssold,
  sd.revenue,
  sd.channelid,
  ad.adspend * (sd.unitssold::decimal / sum(sd.unitssold) over (partition by sd.date, sd.platformid)) as adspent
FROM salesdata sd
JOIN platformmapping pm ON pm.platformid = sd.platformid
JOIN adsdata ad ON ad.platformcode = pm.platformcode AND ad.date = sd.date
    Login or Signup to reply.
  2. 0

    One option is to Sum(unitssold) Over() twice – once partitioned by platformid and once partitioned by platformid and channelid combined and calculate the ratio out of those two summs.

    WITH    -- S a m p l e     D a t a :
  salesdata ( date, platformid, unitssold, revenue, channelid ) AS
    ( Select '2024-07-01', 'ABCD1', 12, 1200, 1 Union All
      Select '2024-07-02', 'ABCD1', 11, 1000, 1 Union All
      Select '2024-07-01', 'ABCD2', 4,  1200, 2 Union All
      Select '2024-07-03', 'ABCD3', 32, 9000, 3 Union All
      Select '2024-07-03', 'ABCD3', 10, 1000, 2
    ), 
  adsdata ( date, platformcode, adspend ) AS 
    ( Select '2024-07-01',  '1A',   1000 Union All
      Select '2024-07-02',  '1A',   1500 Union All
      Select '2024-07-01',  '2B',   4000 Union All
      Select '2024-07-03',  '3C',   4200
    ), 
  platformmapping ( platformid, platformcode ) AS
    ( Select 'ABCD1', '1A' Union All 
      Select 'ABCD2', '2B' Union All
      Select 'ABCD3', '3C'
    ) 

    --    M a i n    S Q L :
Select     sd.date, sd.platformid, sd.unitssold, sd.revenue, sd.channelid,
           --
           Round( ad.adspend * 
                  ( Sum(sd.unitssold::Decimal) 
                        Over( Partition By sd.platformid, sd.channelid ) 
                    / 
                    Sum(sd.unitssold) Over( Partition By sd.platformid ) 
                  ) 
                , 0 ) as adspend
From        platformmapping pm
Inner Join  salesdata sd ON( sd.platformid = pm.platformid )
Inner Join  adsdata ad ON( ad.platformcode = pm.platformcode )
Where       sd.date = ad.date
Order By    sd.platformid, sd.date, sd.unitssold Desc

    /*    R e s u l t : 
date        platformid  unitssold      revenue  channelid   adspend
----------  ----------  ---------  -----------  ---------  --------
2024-07-01  ABCD1              12         1200          1      1000
2024-07-02  ABCD1              11         1000          1      1500
2024-07-01  ABCD2               4         1200          2      4000
2024-07-03  ABCD3              32         9000          3      3200
2024-07-03  ABCD3              10         1000          2      1000    */

    See the fiddle here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search