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

Question

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

Looks like `Group by` with `count` should work. Please show some effort. It is really basic stuff in Sql — Pரதீப், Jan 12 '15 at 12:43
@Rishu For above issue i have to create separate new_table for url_accessed with columns money,news,weather,forecast. Here, i need to populate the data from log_table to new_table where the columns money,news,weather,forecast in this columns the data has to be count of money,count of news and for all columns using pentaho data integration. How can i do this, help me in this task — SRI, Jan 14 '15 at 04:58
@SRI can you start a different question thread on this since the comment question seems a bit different than the one you just have asked in comment above!!.. hope you understand :) — Rishu Shrivastava, Jan 14 '15 at 08:24

score 2 · Answer 1 · answered Jan 12 '15 at 12:45

2

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;

answered Jan 12 '15 at 12:45

Gordon Linoff

1,242,037
58
646
786

here it is showing 2 columns (thedate count(distinct user_key) like 2014-04-06 (3),2014-04-07 (5),2014-04-08 (13) it means (3),(5),(13) are the visits of url by user – SRI Jan 12 '15 at 13:03

Matt · Answer 2 · 2015-01-12T16:16:23.377

1

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

edited Jan 12 '15 at 16:16

answered Jan 12 '15 at 12:45

Matt

14,906
27
99
149

score 1 · Answer 3 · answered Jan 12 '15 at 13:45

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)

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

3 Answers3

Linked