SQL query for joining two tables and pulling repeated entries

SQL query for joining two tables and pulling repeated entries

I'm trying to join two tables that handle customer purchases, or purchase-intents in order to figure out how many customers made a repeat purchase-intent.

I'm trying to join two tables that handle customer purchases, or purchase-intents in order to figure out how many customers made a repeat purchase-intent.

Posting a stripped down version of the DB schema for the problem:

Bid

  • id
  • user_id
  • created_at

Order

  • id
  • bid_id (optional)
  • user_id
  • created_at

User

  • id
  • created_at

Bids signify purchase intent, while Orders signify purchases. A user could be associated with many Bids or Orders.

So a user can start with - creating a Bid, which could then become fulfilled, and generates a Order, or - the user could create an Order without an associating Bid.

Bids aren't directly associated with Orders because a Bid could generate many Orders (one to many).

I'm trying to write a SQL query that pulls users who first created a Bid which became a purchase (i.e have an Order record with a bid_id), and then created another Bid after that.

In English, customers who created a Bid, had the Bid fulfilled, and then placed a new Bid sometime after.

Currently not particular about whether they made a direct purchase (Order without Bid), or had an unfulfilled Bid in between the fulfilled Bid and the new Bid.

Main metric I'm trying to confirm is that they placed a new bid sometime after one of their prior bids got fulfilled.

In trying to solve this, I've been only able to get a list of users who placed more than one bid and had at least one fulfilled. But been unable to get the number of repeat users who first had a prior Bid fulfilled before placing a new bid

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

Learn PostgreSQL, PgAdmin, SQL and JDBC [Combo!] | Simpliv

Learn PostgreSQL, PgAdmin, SQL and JDBC [Combo!]

What is CRUD? | CRUD Operations with SQL and PostgreSQL

What is CRUD? | CRUD Operations with SQL and PostgreSQL - In this course we will be using SQL and PostgreSQL to perform CRUD operations

How to write SQL queries in PostgreSQL

In this tutorial, you will learn how to write simple SQL queries in PostgreSQL.