Skip to content

Daily User Retention for a month mySQL

I have log_event table and which has table as shown below: db<>fiddle I wanted Daily user retention(day-1) for Aug’18. Day-1 retention: Users who registered on day-0 and then opened app on day-1. For ex: In short users who registered on like 15th Aug 2018 and opened app on 16th Aug 2018 Answer You…

Get every unique pair combination of a column in SQL

sql

Lets say I have given table: How do I JOIN / combine the table with itself so I get every possible unique pair combination of the first column: Answer You can do something like this. Cross JOIN is used for cross product

SQL Pivot in BigQuery

I have a SQL table with information about email campaigns that my company has created. Each line of the table is an action that a user has taken on a specific campaign: User ID Campaign Name Status 01 Campaign#1 opened 01 Campaign#1 clicked 01 Campaign#2 opened 02 Campaign#1 opened 02 Campaign#2 opened I want…

How insert if a row not already exists ? (query)

My tables : Wrong migration script : I try insert new row if FirstTable.label == Str Test isn’t already exists but i get an error : SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ” at…

SQL join all values whos items price > 10

sql

I have the following two tables: Items: id price shop_id name 1 10.22 1 apple 2 10.50 1 pear 3 10 2 orange 4 9 2 apricot Shops: id name 1 fruit-shop 2 grocery-shop I am trying to get all the shops that have EVERY item price > 10. I want to retrieve all the items associated with that shop.

SQL query GROUP BY groups

I have something like this: id name totalAmount 1 name1 10 2 name1 20 3 name1 25 4 name2 5 5 name2 12 And need to looks like this: id’s name totalAmount 1,2 name1 30 2,3 name1 45 1,3 name1 35 1,2,3 name1 55 4,5 name2 17 I’m using the STRING_AGG but don’t know how to separated in the first

SQL – Group records together based on value

Currently I have a table with the following values: I would like a resulting SELECT to return the below: eg. The timestamp is grouped by a duplicate some_id Is this possible with SQL? Answer This appears to be just a simple aggregation,

Raw SELECT (without FROM) of most recent 7 days to current

I want to get the query result (e.g. to populate table) of last 7 dates (without times). I know that we can select some scalars without FROM statement. So I ended up with following solution: Please point me to better (and more elegant) solution if there is one. Answer The VALUES, table value constructor, is a…