How to join two tables based on a calculated field?
Nicolas Blanco
I have two SQL queries that output the same kind of output and have the same grouping and order :
select date_trunc('month', inserted_at)::date as date, count(id) from payment_logs where payment_logs.event_name = 'subscription_created' group by date order by date desc;
select date_trunc('month', inserted_at)::date as date, count(id) from users group by date order by date desc;
I would like to join those two results based on the calculated date field (which is the month), and have a result with 3 columns : date, count_users and count_payment_logs.
How can I achieve that? Thanks.
SwaD
Something like this
select plog.date as odata, usr.cntusr, plog.cntlog
from (
select date_trunc('month', inserted_at)::date as date, count(id) cntlog
from payment_logs
where payment_logs.event_name = 'subscription_created'
group by date order by date desc
) plog
join (
select date_trunc('month', inserted_at)::date as date, count(id) cntusr
from users
group by date
) usr on plog.data = usr.data
order by odata desc
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
Related
TOP Ranking
- 1
Failed to listen on localhost:8000 (reason: Cannot assign requested address)
- 2
How to import an asset in swift using Bundle.main.path() in a react-native native module
- 3
Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)
- 4
pump.io port in URL
- 5
Spring Boot JPA PostgreSQL Web App - Internal Authentication Error
- 6
BigQuery - concatenate ignoring NULL
- 7
ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3
- 8
Do Idle Snowflake Connections Use Cloud Services Credits?
- 9
maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class
- 10
Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext
- 11
Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background
- 12
Generate random UUIDv4 with Elm
- 13
Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');
- 14
Is it possible to Redo commits removed by GitHub Desktop's Undo on a Mac?
- 15
flutter: dropdown item programmatically unselect problem
- 16
Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd
- 17
EXCEL: Find sum of values in one column with criteria from other column
- 18
Pandas - check if dataframe has negative value in any column
- 19
How to use merge windows unallocated space into Ubuntu using GParted?
- 20
Make a B+ Tree concurrent thread safe
- 21
ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements
Comments