How to limit user retention SQL query to interval after first user's first activity
I have written a SQL statement that gives user retention, day by day.
SELECT DATE(data_import.time), count(distinct data_import.user_id) AS active_users, count(distinct future_activity.user_id) AS retained_users, count(distinct future_activity.user_id) / count(distinct data_import.user_id)::float AS retention FROM data_import LEFT JOIN data_import AS future_activity ON data_import.user_id = future_activity.user_id AND DATE(data_import.time) = DATE(future_activity.time) - INTERVAL '1 day' GROUP BY 1
I believe that this is giving me user retention after any given day.
How can I limit this to give user retention only after the user's first activity? In other words, this user had activity the day after their first activity.
Let me know if this is unclear of if there are any questions.
See also questions close to this topic
SQL Server function returns question marks instead of real result
I have this function which retrieves first word from String:
CREATE FUNCTION dbo.FIRST_WORD(@value nvarchar(1000)) RETURNS nvarchar(1000) AS BEGIN RETURN CASE CHARINDEX(' ', @value, 1) WHEN 0 THEN @value ELSE SUBSTRING(@value, 1, CHARINDEX(' ', @value, 1) - 1) END END GO
The problem is the data in my table is in
non-ASCIIformat, so when i pass some value to that function, i get question marks instead of a result:
SELECT dbo.FIRST_WORD('ничего не поделаешь')
But if i pass
ASCIIcharacters, for example:
SELECT dbo.FIRST_WORD('hello world')
I tried to add
Nbefore the argument, but it didn't help:
SELECT dbo.FIRST_WORD(N'ничего не поделаешь')
SQL Server loop through a table for every 5 rows
I need to write a stored procedure or table function to return a new data table as a new data source. I wish to loop through the original table for every 5 rows base on the invoice ID column (it's possible not start from 1), the first 5 rows add to the left of the new table and the second 5 rows add to the right of the new table, the third 5 rows to the left and so on.
Thanks in advance!
ORACLE sql - JOINING TABLES
I need help with joining tables according to the ERD/Diagram provided. I did start it off alittle but I am having issues with joining the rest of the tables. This is what I did so far
SELECT first_name, last_name, username, post_title, comment_content FROM profile_info p JOIN club_member cm ON p.member_username = cm.username JOIN blog_post bp ON cm.username = bp.member_username JOIN post_comment pc ON bp.post_id = pc.post_id;
I want to join the POST_PROP, TAG_MEMBER_W_INTEREST and INTEREST. but i am so confused on how to do this. Please can someone help. Below is the diagram:
Also i am sorry I cannot post it other than a link because i do not have reputation points. If someone can edit this id appreciate it. Thank you.
UPDATE: I was able to join the 5 tables, now im left with two other tables which are POST_PROP and POST_COMMENT
--Profile info -> Club member -> Blog post -> TAG MEMBER INTEREST->INTEREST SELECT first_name, last_name, username, post_title,interest_tag FROM profile_info p JOIN club_member cm ON p.member_username = cm.username JOIN blog_post bp ON cm.username = bp.member_username JOIN tag_member_w_interest tg ON cm.username= tg.member_username JOIN interest i ON tg.interest_id = i.interest_id;
Performance tuning on matching strings between two tables columns
I have two tables:
Table 1: Is the filtered or I can say the staging table with contains the filtered records, maybe in millions or in singles records.
Table 2: Is the table with the all inserted data which is of more than hundred millions of records.
Note: Both the tables having more than 40 columns and having index on the required columns.
The following is the sample data to understand the requirement.
create table pattern1 ---This is staging table ( col1 varchar(100) ); insert into pattern1 values('John Mak'),('Omont Boy'),('Will Smith'),('Mak John'); create table pattern2 ( col1 varchar(100) ); insert into pattern2 values('John Mak'),('Smith Will'),('Jack Don');
col1 ---------- John Mak Smith Will
Explaination of expected result: The matching result should in the result it may be in any sequence like
Will Smithshould display.
SELECT p2.* FROM Pattern2 P2 INNER JOIN Pattern1 P1 ON P2.col1 LIKE '%'||P1.col1||'%' --May required more than one columns here to match strings.
The above query took more than 20 minutes and keeps running on hundred millions of records. Any other approache search strings?
How to declare bound cursor with customized column name in pl/pgsql
I would like to use cursor in a function with the table name as a function variable, a simple example would be a select query through cursor.
From the documentation of PostgreSQL I found that I can use
Declare curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;
But when I input
declare curs1 cursor (key integer) for execute 'select ' || quote_ident(colname) || ' from ' || quote_ident(tablename);
ERROR: syntax error at or near "'select '".
On the other hand, if I write the function with
CREATE or replace FUNCTION cursor_hw(colname text,tablename text) RETURNS setof text AS $$ declare curs1 refcursor; BEGIN open curs1 for execute 'select ' || quote_ident(colname) || ' from ' || quote_ident(tablename); for x in curs1 loop return next x; end loop; END; $$ LANGUAGE plpgsql;
It will return
 ERROR: cursor FOR loop must use a bound cursor variable.
Any help would be appreciated, thanks a lot!
pg_hba.conf for Postgresql from Windows Anaconda
I've just installed Postgressql (9.5.4 vc14_0) and Psycopg2 (2.7.5 py36h74b6da3_0) and I'm trying to use them within my Anaconda environment on Windows 10.
Whenever I run
psycopg2.connect("host=localhost user=postgres")in a python interpreter or just
psqlon the command line I get this error:
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?
I've looked at other Stackoverflow questions regarding this, some answers say to look into a pg_hba.conf file although I haven't be able to find any. Where can I find a pg_hba.conf file for my Postgresql in my Anaconda Environment for Windows? If I have to make one, what should go in it? I haven't seen another SO question that uses the same Postgresql-Anaconda-Windows setup that I'm using.
Also I've looked into services.msc but haven't seen a service regarding postgresql.