Skip to content
Advertisement

Tag: postgresql

SQL – create SQL to join lists

I have the following table: Add Data: Find users who share addresses. Expected Results: Question: How do I formulate a SQL query to obtain the expected result? Much appreciated. More info: PostgreSQL 9.5.19 Answer I don’t know if this is the most efficient method, but I can’t come up with something better right now. I assume this will have a

Select rows which have a field in common with another row

I have two tables: products and postings. A product is a consumer product (ex. iPhone X), and a posting is a listing for a product on an online marketplace (ex. eBay posting). A single product has zero or more associated postings. Is there any way to select only postings which have a “sibling”? ie. Select all postings whose product column

Group rows by datediff and then use diff function

I have table in PostgreSQL with timestamp column created_at and integer column user_id. Each row represents some action from user. I need to calculate average user session length. Session is defined as group of actions with time difference less than 10 minutes. When there is difference 10 minutes or more between two user actions, new session started. I was thinking

JSONB sort aggregation

I found this query that suits my needs thanks to this answer here in order to sort fields of data in a JSON document. (Fake, generated random data) SELECT jsonb_agg(elem) FROM ( SELECT * FROM …