Find ratio of rows that have zero in a given column to the total number of rows in SQL – Mysql
Assume I have a table as follow: product date value A 1 2 A 2 0 A 3 3 A 4 0 B 1 1 B 2 0 C 1 1 C 2 2 C 3 4 I am trying…
Athena top 1 result from each group – Postgresql
is there any way to implement top 1 result from each group without using ROW_NUMBER? table_a below has already 1 billion rows, so I want to use the most efficient way. And the below query is what I'm using. SELECT…
Count Common UserIDs using MySQL between Month X and Month X+1, Month X+2, etc
I have the following table named Transactions where the timestamp column is in the Unix timestamp format with the minimum timestamp value being January 2008 and the maximum timestamp value is December 2008 (I'm only concerned with months for this…
SQL: How to select single rows position – Mysql
Let's say I have a table that has 3 fields: (id, name, points). Now initially I want to order them by points and also get the position. So here's my current query that does this: SELECT id, name, points, ROW_NUMBER()…
Get all 1st data of reference key in SQL – Mysql
I have two tables named User and Payment. Where in Payment there is a foreign key of the user table. and here are the records of the Payment Table. SELECT id,User_id FROM payment; id User_id 1 1 2 1 3…
Get the row with minimum property value in group by using MySQL
I have a table (let's say, prod_config) with the below data- Prod_Id Config_Id Config_Type 9856 1 2 9855 93 1 9855 92 2 9855 91 2 9854 93 1 9854 92 2 9854 91 2 9849 93 1 9849 92…
Substituting variable into raw SQL in Laravel
I have a Laravel controller and here is a piece of my code for the SQL query that I made: $query = <<<SQL SELECT day, date, product1_shift1, product1_shift2, product1_shift3, COALESCE(product1_shift1, 0) + COALESCE(product1_shift2, 0) + COALESCE(product1_shift3, 0) AS total_product1_qty FROM…
In MySQL, how do I do a bulk update to a LIKE query, removing a character?
I have the following MySQL query that pulls back 400 records: SELECT email FROM user WHERE email LIKE 'GMSD%@law.com' Returns: [email protected] through [email protected] What I want to do is remove the K from all 400 of these e-mail addresses in…
Json – How to get the Incremental data from config file to get last 5 days data only
Currently i am using a config file in python to get incremental data from an api. Below is a snippet of the config file [ { "id" : 123, "input":{ "name" : , "path" : , "format": , "paramters":{ "table_name"…
SQL Count Distinct over Rolling Window on Timestamp – Mysql
I have this SQL query: SELECT COUNT(DISTINCT CustomerName) over( ORDER BY OrderTimestamp RANGE BETWEEN INTERVAL 2 hour PRECEDING AND CURRENT ROW ) AS count_per_time FROM Orders WHERE CustomerName IS NOT null AND CustomerName != '' but it doesn't work because…