CODEWITHSUNDEEP
sqlsql-serverdatabase-design
SRI
SRI

Reputation: 611

how to find how many customers accessed application daily on date basis

Hi I have a mobile application in that MobApp, customer can see the information about

money,news,forecast,advice in that i need to find out the count for how many times customers visited

money,news,forecast,advice separately on daily basis based on date.

The log table consists of columns like 

id  user_key  url_accessed   parameters           timestamp 
.. .........  ............   ..........          ............
1   sdhbhjbh  /user/money  mobile_no=9999999  2015-01-08 13:06:33
2   cnbjknjk  /user/news   user_key=534d2135  2014-09-06 26:02:25

Here I have to find how many times user accessed the url daily on date basis and count for accessing

the url of money,news,forecast,advice

Here I am using SQL Server with Pentaho data integration

Please help in this issue

Upvotes: 1

Views: 139

Answers (3)

Matt
Matt

Reputation: 15071

Using COUNT and GROUP BY it counts the unique number of users that have accessed each URL.

SELECT COUNT(DISTINCT user_key), url_accessed
FROM logtable
GROUP BY url_accessed

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44316

SELECT 
  COUNT(DISTINCT user_key) user_key,
  url_accessed,
  CAST(timestamp as date) datestamp
FROM 
  yourtable
WHERE 
  url in ('/user/money', '/user/news', '/user/forecast', '/user/advice')
GROUP BY 
  url_accessed,
  CAST(timestamp as date)
ORDER BY 
  CAST(timestamp as date)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Presumably, users can access the app more than once in a day. If so, you want count(distinct):

select cast(timestamp as date) as thedate, count(distinct user_key)
from log l
group by cast(timestamp as date)
order by thedate;

Upvotes: 2

Related Questions