I’m creating a web application and I encountered a problem with importing data to a table in a postgress database. I have excel with id_b and id_cat(books id and categories id) books have several categories and categories can be assigned to many books, excel looks like this: excel data It has 30 000 rec…
Tag: sql
Is there any method to find number of columns having data in pyspark data frame
I have a pyspark data frame that has 7 columns, I have to add a new column named “sum” and calculate a number of columns that have data (Not null) in the sum column.Example a data frame in which yellow highlighted part is required answer Answer This sum can be calculated like this: Hope this helps…
Sql syntax error while doing left outer Join in SQL
I’m using oracle database and trying to delete duplicate records from that. For the same I’ve written a below query but getting this error Its giving red indicator under outer keyword in Left outer join in query. Query: Answer Oracle does not permit JOINs in DELETE queries. This is clear if you lo…
How to update database after receiving PayPal IPN
I am trying a simple listener to update my user info database (like setting their account type from FREE to PREMIUM) after receiving the PayPal IPN. Upon checking the IPN history, the IPN is successfully sent to my listener but can’t seem to update my database. I wanted to update the user’s accoun…
Deduplicate table SQL with nested rows (type STRUCT)
I have a SQL table (in BigQuery) with possible duplicated rows. The table has over 20 columns, some of them are nested (data type “STRUCT)”. I want to deduplicate the table. I can’t simply query SELECT DISTINCT * because I get an error Query error: Column options of type STRUCT cannot be use…
Run UPDATE query while the INSERTs are running
I have a DB table which size is around 100,000 rows. I Need to run an UPDATE query on that table (affecting every row) which runs for approximately 25 minutes. During this 25 minutes, there will be approximately 300 INSERTs to the same table. I’m wondering whether those INSERTs will run? Will they be bl…
Calculating bounding box from latitude and longitude pairs
Google returns the current lat and lon, but the lower left lat and lon and upper right lat and lon are calculated. I have not been able to figure out how it’s calculated. Does anyone know if it’s a static distance? a percentage difference? How is it calculated? Chicago, IL New York UPDATE: Knowing…
How to Find People Who did Transaction Twice on The Same Day and Who did Transaction Consecutively in Less than 10 Minutes
I have another table which looked like this: I need to write two queries: Find users who did transactions twice on the same day, what product were they bought and how much were they spend on the 1st transaction. Find which users did transaction consecutively less than 10 minutes! I am very appreciating for yo…
Retrieve text between two periods in a value
I’ve been spinning around a bit on how to accomplish this in SQL DW. I need to extract the text between two periods in a returned value. So my value returned for Result is: I’m trying to extract the values between period 1 and 2, so the red portion above: The values will be a wide variety of lengths. I’ve…
Postgres recursive query – get path to root given a leaf node
I have a leaf node in a tree structure and I need a Postgres query to recursively traverse the parent nodes to get me the full path to my leaf node. My table looks like this – the problem being there is no specific root node (i.e the top-most managers end up reporting to each other) Given a user such